Tricky Group By Order By Query
Jul 23, 2005
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.
Help!?
View 5 Replies
ADVERTISEMENT
Jul 20, 2005
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
View 3 Replies
View Related
Nov 2, 2006
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
Quote:
View 15 Replies
View Related
Mar 21, 2004
I have a table as follows:
Fixtures(ID, HomeTeam, AwayTeam, WeekNumber)
Each team plays alternately at home then away throughout the course of a season.
I want perform 2 seperate queries on this table.
Query 1:
I want to select a particular teams opposition for the entire season.
Query 2:
I want to select a particular teams opposition for a particular weekNumber.
Thanks
View 3 Replies
View Related
Feb 9, 2001
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
please help...
View 3 Replies
View Related
Aug 31, 1999
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.
View 1 Replies
View Related
Aug 29, 2001
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.
Example.
Query 1 returns
Item Expected
--------------------
Lion 2
Tiger 2
Bear 2
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.
Thanks
Josh
View 3 Replies
View Related
Feb 9, 2001
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
please help...
View 2 Replies
View Related
Nov 16, 2005
Hi,
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?
//Mr
View 6 Replies
View Related
Feb 22, 2007
I have a data table like this:
MachineIDProductSales
-------------------------------
1Magazine$20.00
1Drink$30.00
2Drink$30.00
3Magazine$30.00
3Drink$40.00
4Magazine$30.00
5Food$40.00
5Drink$30.00
6Drink$40.00
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.
Here is some setup T-SQL code:
CREATE TABLE SalesData
(
MachineIDINTEGER,
ProductNameVARCHAR(50),
SalesMONEY
)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Magazine', 20)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (2, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Magazine', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Drink', 40)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (4, 'Magazine', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Food', 40)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (6, 'Drink', 40)
Of course, this is a much simplified version of the real business problem I'm facing. Any help is greatly appreciated. Thanks!
View 5 Replies
View Related
Aug 10, 2007
Hello
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).
Example:
(Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <--- Would pass
(Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <--- Would pass
(Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <--- Would not pass
How can I accomplish that?
Thanks a lot.
View 2 Replies
View Related
Jul 20, 2005
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!
View 4 Replies
View Related
Aug 3, 2006
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?
Thanks,
elinde
View 6 Replies
View Related
Mar 26, 2008
Hi All,
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?
Thanks much,
Siva.
View 11 Replies
View Related
Apr 18, 2004
Let's say that I have three tables:
Buyer
------
ID
Name
Adress
Session
-------
ID
Date
Pageviews
Buyer
Orders
-------
ID
DatePaid
Session
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.
Any help would be enourmously appriceated.
View 3 Replies
View Related
Jul 20, 2005
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
View 7 Replies
View Related
Jul 23, 2005
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?
View 3 Replies
View Related
Feb 28, 2002
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.
Many thanks,
Leonard
View 1 Replies
View Related
Mar 8, 2008
Hi,
My tables:
Product
- productID
- name
- price
Inventory
- productID
- stockCount
- timestamp
So each day the Inventory table has a new row for each productID with its stock count.
How can I create a report for the total products sold from one day to another? Or from what a dateStart from a dateEnd (i.e. a range)
Example:
ProductID StockCount TimeStamp
1 10 2008/03/07
1 7 2008/03/08
So you can see that 3 products were sold in the last day.
View 4 Replies
View Related
Oct 23, 2006
Does the order of columns make a difference in the GROUP BY?Woulde GROUP BY A, B, Creturn different results thanGROUP BY C,A,B ?
View 3 Replies
View Related
May 21, 2014
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.
View 3 Replies
View Related
Apr 15, 2008
hi,
my table data:
Sensor_Serial_No date_time Attribute_Flag
-----------------------------------------------------
2 2008-03-03 01:00:00 5
2 2008-03-03 06:00:00 5
my result should be like this
Sensor_Serial_No no of times occured Attribute_Flag lastoccurence
2 2 5
2008-03-03 06:00:00
please give me query for this please
View 2 Replies
View Related
Sep 22, 2007
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.
Name= Test1, Price= 25
Name= Test2, Price= 20
Name= Test1, Price= 15
Name= Test3, Price=30
When listing the products it will show the following order:
Test2, 20
Test1, 25
Test3, 30
However I want it to display:
Test1, 15
Test2, 25
Test3, 30
Thanks for any help.
Daniel
View 3 Replies
View Related
Apr 9, 2008
Hi All,
Please let me know if both query fetch the same result
Select Col1, Col2, Col3, Col4 from Table1 Group By Col1, Col2, Col3, Col4
Order By Col1, Col2
Select Col1, Col2, Col3, Col4 from Table1
Order By Col1, Col2
Thanks,
Muthu
View 7 Replies
View Related
Sep 30, 2007
Hi,
A SSB newbie question:
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"?
Thanks,
Ido
View 3 Replies
View Related
Jul 7, 2014
I am having below schema.
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.
View 5 Replies
View Related
Sep 21, 2015
I am executing a CUBE which is having 9 measure groups, i need to know order of the execution of the measure groups.??
ex:
1. fsales
2.ftranssales
3.fitem
4.fstores..............etc...
I know, the dimension are executed first, but not aware of the order of execution of measure group.
View 4 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Dec 19, 2014
Sample Data
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE CheckRegistry (
CheckNumber smallint,
[code]...
How can I get the result orderd by the month number?
View 3 Replies
View Related
Feb 13, 2008
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?
View 1 Replies
View Related
Mar 30, 2015
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
[Code]....
View 2 Replies
View Related
May 27, 2015
I have a request for being able to show ordertotals for those orders that contains a specific productgroup.
Case
Order 1  ProductGroup  Value
A 20
B 40
C 40
Total 100
Order 2  ProductGroup  Value
A 20
DÂ 40
Total 60
So if i slice OrderTotal by ProductGroup the result will be
A 160
B 100
C 100
D 60
ALL Â Â Â Â Â Â 160
how to solve this.
View 9 Replies
View Related
Sep 29, 2015
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, ...
View 4 Replies
View Related