Stuck with 2 queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stuck with 2 queries

Author

Topic

elle39
Starting Member

6 Posts

Posted - 2008-07-21 : 23:49:17

How can I get:

1) only the Top 3 InvoiceTotals for each Vendor in each State.

2) 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 !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 23:50:41

SQL 2000 or 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

elle39
Starting Member

6 Posts

Posted - 2008-07-21 : 23:55:24

quote:
Originally posted by khtan

SQL 2000 or 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

Sorry, It's SQL 2005

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-22 : 00:00:39

[code]-- Query 1
SELECT InvoiceTotals, VendorName, VendorState
FROM
(
SELECT InvoiceTotals, VendorName, VendorState,
row_no = row_number() OVER (PARTITION BY VendorName ORDER BY InvoiceTotals DESC)
FROM
(
SELECT InvoiceTotals = SUM(InvoiceTotals),
v.VendorName,
v.VendorState
FROM Vendors v
INNER JOIN Invoices i ON v.VendorID = i.VendorID
GROUP BY v.VendorState, v.VendorName
) v
) d
WHERE row_no <= 3

-- Query 2
SELECT InvoiceCount, VendorName, VendorState
FROM
(
SELECT InvoiceCount, VendorName, VendorState,
row_no = row_number() OVER (PARTITION BY VendorName ORDER BY InvoiceCount DESC)
FROM
(
SELECT InvoiceCount = COUNT(*),
v.VendorName,
v.VendorState
FROM Vendors v
INNER JOIN Invoices i ON v.VendorID = i.VendorID
GROUP BY v.VendorState, v.VendorName
) v
) d
WHERE row_no = 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

elle39
Starting Member

6 Posts

Posted - 2008-07-22 : 06:41:50

quote:

Originally posted by khtan

-- Query 1
SELECT InvoiceTotals, VendorName, VendorState
FROM
(
	SELECT	InvoiceTotals, VendorName, VendorState,
		row_no = row_number() OVER (PARTITION BY VendorName ORDER BY InvoiceTotals DESC)
	FROM
	(
		SELECT	InvoiceTotals = SUM(InvoiceTotals), 	
			v.VendorName, 
			v.VendorState
		FROM	Vendors v
			INNER JOIN Invoices i	ON	v.VendorID	= i.VendorID
		GROUP BY v.VendorState, v.VendorName
	) v
) d
WHERE	row_no	<= 3

-- Query 2
SELECT InvoiceCount, VendorName, VendorState
FROM
(
	SELECT	InvoiceCount, VendorName, VendorState,
		row_no = row_number() OVER (PARTITION BY VendorName ORDER BY InvoiceCount DESC)
	FROM
	(
		SELECT	InvoiceCount = COUNT(*), 	
			v.VendorName, 
			v.VendorState
		FROM	Vendors v
			INNER JOIN Invoices i	ON	v.VendorID	= i.VendorID
		GROUP BY v.VendorState, v.VendorName
	) v
) d
WHERE	row_no	= 1

KH
[spoiler]Time is always against us[/spoiler]

Thanks I will try this query. Any chances of simplifying this especially the second nested query in FROM as I have only learnt nested queries. Haven't learnt partition yet.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 07:03:08

quote:

Originally posted by elle39

quote:

Originally posted by khtan

-- Query 1
SELECT InvoiceTotals, VendorName, VendorState
FROM
(
	SELECT	InvoiceTotals, VendorName, VendorState,
		row_no = row_number() OVER (PARTITION BY VendorName ORDER BY InvoiceTotals DESC)
	FROM
	(
		SELECT	InvoiceTotals = SUM(InvoiceTotals), 	
			v.VendorName, 
			v.VendorState
		FROM	Vendors v
			INNER JOIN Invoices i	ON	v.VendorID	= i.VendorID
		GROUP BY v.VendorState, v.VendorName
	) v
) d
WHERE	row_no	<= 3

-- Query 2
SELECT InvoiceCount, VendorName, VendorState
FROM
(
	SELECT	InvoiceCount, VendorName, VendorState,
		row_no = row_number() OVER (PARTITION BY VendorName ORDER BY InvoiceCount DESC)
	FROM
	(
		SELECT	InvoiceCount = COUNT(*), 	
			v.VendorName, 
			v.VendorState
		FROM	Vendors v
			INNER JOIN Invoices i	ON	v.VendorID	= i.VendorID
		GROUP BY v.VendorState, v.VendorName
	) v
) d
WHERE	row_no	= 1

KH
[spoiler]Time is always against us[/spoiler]

Thanks I will try this query. Any chances of simplifying this especially the second nested query in FROM as I have only learnt nested queries. Haven't learnt partition yet.

take this as an oppurtunity. Use and learn it. use books online for assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources