July 22, 2008 at 6:36 am
In SQL Server 2005, How can I get:
1st query : only the Top 2 highest InvoiceTotals for each Vendor in each State.
2nd query : the vendornames that has supplied the highest number of Invoices for each state.
For the first query, I know this is wrong but what should i be doing??
Select top 2 Invoicetotals, vendorname, vendorstate
from vendors join invoices on vendorID
Group by vendorstate, Vendorname
Order by InvoiceTotal DESC
For the second query, I didn't quite get it:
Select vendorname, vendorstate, count(*) as InvoiceQty
from vendors join invoices on vendorID
Group by VendorState, VendorName
Order by InvoiceTotal DESC
Help !
July 22, 2008 at 9:12 am
It isn't clear what "InvoiceTotal" is supposed to be. Could you post the table definitions and some sample data?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 22, 2008 at 9:25 am
If you reallywant to do this in SQL...
This'd be UGLY, but for the first query, you could union 50 occurrences of your statement with a tweak (your original code indented for clarity):
Select * from
( Select top 2 Invoicetotals, vendorname, vendorstate
from vendors join invoices on vendorID
= Invoices.VendorID
Group by vendorstate, Vendorname
where vendorstate = 'NY'
Order by InvoiceTotal DESC
) NY
union ... 49 more "Select * from (Select top 2...)" statements
Stick it in a sproc so you don't have to look at it. No promises on the speed, but might be okay.
Another option would be a cursor with a temp table.
July 22, 2008 at 9:30 am
dcorbitt (7/22/2008)
Stick it in a sproc so you don't have to look at it. No promises on the speed, but might be okay.Another option would be a cursor with a temp table.
The ranking functions in SQL 2005 are usually a much nicer solution. Just don't have enough info yet to say how exactly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply