Hello, I'm trying to find the most optimal way to perform a tricky
query. I'm hoping this is some sort of standard problem that has been
solved before, but I'm not finding anything too useful so far. I have
a solution that works (using subqueries), but is pretty slow.
Assume I have two tables:
[Item]
ItemID int (Primary Key)
ItemSourceID int
ItemUniversalKey uniqueidentifier
Price int
[Source]
ItemSourceID
Priority
I'm looking for a set of ItemIDs that match a query to the Price
(something like Price < 30), with a unique ItemUniversalKey, taking the
first item with each key according to Source.Priority.
So, given Item rows like this:
1 2 [key_one] 15
2 2 [key_two] 25
3 1 [key_one] 15
and Source rows like this:
1 1
2 2
I want results like this:
2 2 [key_two] 25
3 1 [key_one] 15
Row 1 in Item would be eliminated because it shares an ItemUniversalKey
with row 3, and row 3's Source.Priority is lower than row 1.
Dear GroupI wonder whether you can push me in a direction on how to design thefollowing statement. I'm looking for a SELECT with some tricky ORDERBY.The database table looks like this:MenuID TabText SubTabID TabOrderID------- ----------- ----------- -----------1 Main 0 12 Cars 0 23 Boats 0 34 Planes 0 45 Pick-Ups 2 16 Campers 2 2The result should look like this:MainCarsPick-UpsCampersBoatsPlanesNotice that 'Pick-Ups' and 'Campers' are a subcategory of 'Cars' andmust appear in the result directly following 'Cars'.In more detail:'Main', 'Cars', 'Boats' and 'Planes' are top-level categories and'Pick-Ups' and 'Campers' are subcategories of 'Cars'. The SubTabIDvalue of an item identifies to what top-level category a subcategorybelongs.The TabOrderID specifies in what order the items should be sorted,e.g. 'Pick-Ups' comes first and 'Campers' second.Thanks very much for your help & efforts!Martin
Hi,I have a tricky SQL query problem that I'm having probs with.I have a table which resembles something like thisDate | Price1 | Price2 | Price301 Jan 2006 | 100 | 100 | 10002 Jan 2006 | 100 | 100 | 10003 Jan 2006 | 100 | 100 | 10004 Jan 2006 | 115 | 100 | 10005 Jan 2006 | 115 | 100 | 10006 Jan 2006 | 115 | 115 | 11507 Jan 2006 | 115 | 100 | 10008 Jan 2006 | 100 | 100 | 10009 Jan 2006 | 100 | 100 | 100and I want to write a query/view that will return this
I'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...
the problem is: The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.
I want to find the records where Keys values 1, 2 has the same Item value (and if they don't I don't want to find any post at all, of course)
something like this: SELECT * FROM table1 WHERE ???
ex of table1: Key | Item ---------- 1 | 1 1 | 2 2 | 2 3 | 1
I have a table that keeps track of account access errors. When there are three access errors in one day, the account is locked out. How can i construct a query to select any accounts that have three access errors on the same date. The pertinent fields would be ACCOUNTNUMBER AND ERRORDATE.
I have a feeling it is very easy to do what I want. But I don't know how. I have 2 queries that return 2 results sets. I'd like to have just 1 query that returns 1 result set that contains all the data of the 2 results sets.
Query 2 returns Item Actual ------------------- Lion 1 Bear 1
What I want is 1 query that will combine the results Item Expected Actual ------------------------------- Lion 2 1 Tiger 2 0 Bear 2 1
I tried using a unions between the 2 queries but that doesn't work. I am pulling my hair out. I have been struggling with this for several days now. Any help would be greatly appreciated.
I'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...
the problem is: The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.
I want to find the records where Keys values 1, 2 has the same Item value (and if they don't I don't want to find any post at all, of course)
something like this: SELECT * FROM table1 WHERE ???
ex of table1: Key | Item ---------- 1 | 1 1 | 2 2 | 2 3 | 1
I need to return a number of records at specifik days, i do it with this query;
SELECT LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) AS Days, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) AS Numbers_total, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) AS Numbers_In FROM tb_SecurityLog WHERE (CONVERT(varchar, CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND (SL_PolicyName LIKE N'%') GROUP BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) ORDER BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)
i also need to have a criteria at that second COUNT and if the criteria is not met that row should not be counted, is this possible at all?
One of the reports the user needs to see looks like this:
ProductNumber of MachinesTotal Sales Magazine/Drink2$120.00 Drink2$70.00 Magazine1$30.00 Food/Drink1$70.00
To clarify:
There are two magazine/drink machines (ID 1 and 3) There are two drink only machines (ID 2 and 6) There is one magazine only machine (ID 4) There is one food and drink machine (ID 5)
How do I do this query?
Ideally, I wouldn't limit the number of products in a given machine, but I can do so if necessary.
I'm using SQL Server 2000 so I can't use the newer PIVOT/UNPIVOT functions in SQL Server 2005.
I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).
I have 2 tables joined together by the IDs, People and the pets theyownPEOPLEID NAME1 JohnSMith2 JaneDoePETSID PET1 Dog2 Cat2 Hamster2 Hamster2 FishI have create another where the PETS are in one column separated bysemi-colons and removing the dupsNEW TABLEID NAME ALLPETS1 JohnSmith Dog2 JaneDoe Cat;Hamster;FishWhat is the best way to do it? The only way I can think of is to runan update where it checks to see if the value already existsTHanks!
I'm self-taught at SQL, so this may be an easy one for others, but I can't even figure out how to search for an answer.
I need to put together a query as a datasource for a chart showing the firm's top ten clients by revenue AND the top ten clients by hours worked. It's easy to do either query separately, but the problem comes in when the two are combined. Then top ten by revenue doesn't always include all the top ten by hours clients, and vice versa [at the moment, I'm running a top twenty for each, then hand-compiling the top ten in Excel--oy!].
How can I write a query that will guarantee to include the top ten of both revenue and hours lists?
We've a table which has about 1.5 mil records. The table has info like AccountNum FName LName, Flag, Address etc. There are duplicate Account Numbers.
What we're trying to accomplish is:
If I query the table as in the following, SELECT AccountNum, Flag, COUNT(*) AS CountStar FROM Table1 GROUP BY AccountNum, Flag HAVING COUNT(*) > 1
I'll get something like this:
AccountNum Flag CountStar 1234567 Y 2 9876543 Y 4 9184382 Y 3 7439831 Y 5 6958373 Y 4 ....... . . ....... . . etc..
First, I want to display the result as in the following:
AccountNum Flag 1234567 Y (along with other columns) 1234567 Y 9876543 Y 9876543 Y 9876543 Y 9876543 Y 9184382 Y 9184382 Y 9184382 Y ....... . ....... . etc...
Is it possible?
Once I've the result in the above format, the next step in plan is to update the flag with 'N' leaving the first occurrence flag as 'Y' but all others as 'N' for a particular AccountNum. Once I do this, the result should look like the following:
AccountNum Flag 1234567 Y (along with other columns) 1234567 N 9876543 Y 9876543 N 9876543 N 9876543 N 9184382 Y 9184382 N 9184382 N ....... . ....... . etc...
Can anybody suggest any ideas how to accomplish this?
Now, I've been racking my brain on how to list the Buyers and the number of related rows in the Orders table. Add to this that I only want to count the Orders where DatePaid IS NOT NULL.
I'm having much difficulty figuring out how to write the followingquery. Please help!I have this table:EventEventId int Primary KeyPatientId intSeverityLevel intWhat I want returned in my query is a list of all (distinct)PatientIds appearing in Event, with the *most severe* EventId returnedfor each Patient. The higher the value of SeverityLevel, the moresevere that Event is considered to be.The problem I am having is that I can't figure out how to (a) group byPatientId, AND (b) return the EventId of the highest-severity Eventfor *each* PatientId (Order By SeverityLevel Desc).So if my table contained:EventId PatientId SeverityLevel------- --------- -------------1 1 02 1 13 1 54 2 55 2 2I would want my result set to be:PatientId EventId--------- -------1 32 4since events 3 and 4 are the most severe events for patients 1 and 2,respectively.Any help would be greatly appreciated. This seems to be something thatcould be handled easily with a FIRST() aggregate operator (as in MSAccess) but this is apparently lacking in SQL Server. Also note theremay be multiple Events with a given PatientId and SeverityLevel, inthat case I'd want only one of the EventIds (the Max() one).Many thanks,Joel ThorntonDeveloper, Total Living Choices<joelt@tlchoices.com>(206) 709-2801 x24
I have a SQL7 database that was installed as case-insensitive./* Sort Order = 52, Case-insensitive dictionary sort order. */This database contains a table that has a varchar column which containsdata such as:'JUDICIARY; EDUCATION; Subcommittee on Justice and Judiciary''Subcommittee on Justice and Judiciary; TRANSPORTATION''Subcommittee on Cities; JUDICIARY; TRANSPORTATION'I want to write a SELECT statement that gives me only those rows (1stand 3rd) that have JUDICIARY (not Judiciary) in the varchar column.This is SQL7 so I can't use COLLATE.I triedSELECT mycolFROM mytableWHERE mycol LIKE '%JUDICIARY%'AND CAST(SUBSTRING(mycol ,PATINDEX('%JUDICIARY%',mycol),LEN('JUDICIARY')) AS VARBINARY) = CAST('JUDICIARY' AS VARBINARY)But this leaves out the row with JUDICIARY and Judiciary in it (onlyreturns 3rd row).Any suggestions?
I need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table: ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel: ColID as longint, ColA as int, ColB as int Child table -- Door: ColID as longint, ColA as int, ColB as int Child table -- Window: ColID as longint, ColA as int, ColB as int ..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
I have a a grid (Fig-1) where i have LineID and corresponding RankValue. I want to sort out the Grid like (Fig-2) where It will be sorted based on Rank Value(Higher to lower) but LineID group should maintain. I am using SqlServer 2008.
I have the following statement that is working just fine:
SQL = "select* from products GROUP BY name ORDER BY price"
I have many products with the same name but with different prices and I want to display unique records along with other information but I want to display the cheapest product first. It is displaying the first record in the table with that name but not the cheapest distinct record in the list of all distinct product names. Not sure of the above makes sense so this is the kind of data in the table.
Looking in a various resources, i see that a conversation group guarantees receiving messages EOID, which means that each message will be received only once, and that messages will be received in the order they were sent.
However, the documentation explicitly states that only dialogs guarantee messages order. So, I€™m a little bit confused, what's the "In-Order" stand for in "Exactly Once In Order"?
CREATE TABLE #Turnover ( location varchar(50), Total int )
insert into #Turnover (location,Total) values('A', 500) insert into #Turnover (location,Total) values('AB', 200) insert into #Turnover (location,Total) values('ABC', 100) insert into #Turnover (location,Total) values('BA', 100) insert into #Turnover (location,Total) values('BAC', 500) insert into #Turnover (location,Total) values('BAM', 100)
Now i want output order by total but same time i want to create two groups. i.e. location starting with A and order by total and after locations starting with B and order by total.
Using SQL Server 2005 (9.0.3042), I can not get a full-text search query to work with order by or group by. For example, if I run the following query...
SELECT s.networkID, i.interID
FROM Interactions i
INNER JOIN CONTAINSTABLE(Interactions, text, 'ipo') t ON t.[key] = i.interID
INNER JOIN Sessions s ON i.sessionID = s.sessionID
WHERE i.startTime BETWEEN 1051772400000 AND 1054537199000
It returns two rows immediately.
If I add an "order by" or "group by" like...
SELECT s.networkID, i.interID
FROM Interactions i
INNER JOIN CONTAINSTABLE(Interactions, text, 'ipo') t ON t.[key] = i.interID
INNER JOIN Sessions s ON i.sessionID = s.sessionID
WHERE i.startTime BETWEEN 1051772400000 AND 1054537199000
ORDER BY s.networkID, i.interID
The CPU shoots to 100% and I have to kill the query. I have tried many variations of the above and I can not get the query to work with order by or group by. Is this a bug in SQL Server? Is there a fix or workaround?
In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null drop table #left_table; if OBJECT_ID('tempdb..#right_table') is not null drop table #right_table; create table #left_table
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
