July 22, 2008 at 6:28 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 12:16 pm
The first one looks like it needs a Sum() function in it. Look up aggregate functions, and "SUM" specifically, in Books Online. That should give you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 23, 2008 at 5:33 am
Hi Pal,
try this
IF EXISTS
(SELECT * FROM SYS.OBJECTS
WHERE OBJECT_ID =
OBJECT_ID(N'[DBO].[MYTABLE]')
AND TYPE IN (N'U'))
DROP TABLE [DBO].[MYTABLE]
GO
CREATE TABLE MYTABLE
(InVoiceTotal INT, vName VARCHAR(50),
State VARCHAR(50))
GO
insert into mytable select 500,'Jen','LA'
insert into mytable select 1000,'Jiong','LA'
insert into mytable select 25,'Sandra','Mator'
insert into mytable select 35,'Chun','Chang'
insert into mytable select 500,'Jiong','LA'
insert into mytable select 100,'Jiong','LA'
insert into mytable select 100,'Jiong','Miami'
insert into mytable select 21,'Yuki','Fukushima'
insert into mytable select 1000,'Jen','LA'
insert into mytable select 1500,'Jen','NY'
insert into mytable select 2500,'Jen','LA'
insert into mytable select 25,'Sandra','Mator'
insert into mytable select 75,'Sandra','Mator'
go
SELECT * FROM mytable
WITH TOPRESULT
AS
(
SELECT [ROWNO]= ROW_NUMBER() OVER (PARTITION BY vNAME,State ORDER BY vName,State),InVoiceTotal,vNAME,State
FROM
MYTABLE
)
SELECT * FROM TOPRESULT
WHERE ROWNO<3
July 23, 2008 at 6:10 am
Thank you all. I have not had a chance to test this. Will revert back in a day.
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply