SELECT TOP n - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT TOP n

Author

Topic

rythm123us
Starting Member

27 Posts

Posted - 2005-09-17 : 12:56:48

I wonder if there is a way to select TOP n records for each product in a
query. Hopefully my results will show what I mean

DECLARE @Sales TABLE(SalesID INT, ProductID INT, Quantity INT, Price MONEY, Cost MONEY)

INSERT @Sales(SalesID, ProductID, Quantity, Price, Cost)
SELECT 20936, 1554, 2, 6.99, 4.91 UNION ALL
SELECT 20936, 1555, 5, 11.99, 9.54 UNION ALL
SELECT 20936, 1554, 4, 5.99, 4.91 UNION ALL
SELECT 20935, 1554, 1, 6.99, 4.91 UNION ALL
SELECT 20935, 721, 1, 10.99, 8 UNION ALL
SELECT 20934, 786, 1, 9.99, 7.35 UNION ALL
SELECT 20934, 786, 2, 9.99, 7.35 UNION ALL
SELECT 20934, 1554, 1, 6.99, 4.91 UNION ALL
SELECT 20934, 721, 2, 10.99, 8 UNION ALL
SELECT 20933, 1554, 1, 6.99, 4.91 UNION ALL
SELECT 20933, 1554, 1, 5.99, 4.91

DECLARE @Products TABLE(ProductID INT, Product VARCHAR(55), CategoryID INT)

INSERT @Products(ProductID, Product, CategoryID)
SELECT 1554, 'Woodbridge Chardonnay, 750 ml', 39 UNION ALL
SELECT 721, 'SHARPE HILL RED SERAPH, 750 ml', 38 UNION ALL
SELECT 786, 'BUD , 12 oz 12 pack Bottle(s)', 3 UNION ALL
SELECT 1555, 'Woodbridge Chardonnay, 1.5 L', 39

SELECT P.Product, S.Quantity
FROM @Sales S INNER JOIN
@Products P ON P.ProductID = S.ProductID

Product Quantity
------------------------------------------------------- -----------
Woodbridge Chardonnay, 750 ml 1
Woodbridge Chardonnay, 750 ml 1
Woodbridge Chardonnay, 750 ml 1
Woodbridge Chardonnay, 750 ml 2
Woodbridge Chardonnay, 750 ml 4
Woodbridge Chardonnay, 750 ml 1
SHARPE HILL RED SERAPH, 750 ml 1
SHARPE HILL RED SERAPH, 750 ml 2
BUD , 12 oz 12 pack Bottle(s) 1
BUD , 12 oz 12 pack Bottle(s) 2
Woodbridge Chardonnay, 1.5 L 5

What I would like to see: (TOP 2)
Product Quantity
------------------------------------------------------- -----------
Woodbridge Chardonnay, 750 ml 1
Woodbridge Chardonnay, 750 ml 1
SHARPE HILL RED SERAPH, 750 ml 1
SHARPE HILL RED SERAPH, 750 ml 2
BUD , 12 oz 12 pack Bottle(s) 1
BUD , 12 oz 12 pack Bottle(s) 2
Woodbridge Chardonnay, 1.5 L 5

Any help will be great.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-09-17 : 13:52:09

Hi there are couple of ways to do so..

first which is easiers..

SELECT Top 2 P.Product, S.Quantity
FROM @Sales S INNER JOIN
@Products P ON P.ProductID = S.ProductID

Tnis will select the top 2 records that will display it to you ..

and other way is using
Set RowCount 2

SELECT P.Product, S.Quantity
FROM @Sales S INNER JOIN
@Products P ON P.ProductID = S.ProductID

Set RowCount 0

This will also select the top 2 records..

Complicated things can be done by simple thinking

rythm123us
Starting Member

27 Posts

Posted - 2005-09-17 : 16:49:28

I think you did not understand my question.
Your suggested query:

SELECT Top 2 P.Product, S.Quantity
FROM @Sales S INNER JOIN
@Products P ON P.ProductID = S.ProductID

WOULD RETURN

Product Quantity
------------------------------------------------------- -----------
Woodbridge Chardonnay, 750 ml 1
Woodbridge Chardonnay, 750 ml 1

That is not the result I am looking for.

Also ROWCOUNT is only for backward compatibility and should not be used after 6.5/7.0 (I am not sure which one).

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-17 : 17:15:56

ROWCOUNT works fine through SQL Server 2000, it will be superseded by enhancements to TOP in SQL Server 2005.

The biggest problem with the example you posted is that the TOP condition is not defined by a value, such as Quantity, Total Sales Amount, etc. You cannot rely on physical position in a relational database; the concept is meaningless because there are no "row numbers". In order for TOP to make sense, you must include an ORDER BY clause to order the data by its value, not position.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-17 : 18:14:01

Well, here we go again.


DECLARE @Sales TABLE(SalesID INT, ProductID INT, Quantity INT, Price MONEY, Cost MONEY)

INSERT @Sales(SalesID, ProductID, Quantity, Price, Cost)
	SELECT 20936, 1554, 2, 6.99, 4.91 UNION ALL
	SELECT 20936, 1555, 5, 11.99, 9.54 UNION ALL
	SELECT 20936, 1554, 4, 5.99, 4.91 UNION ALL
	SELECT 20935, 1554, 1, 6.99, 4.91 UNION ALL
	SELECT 20935, 721, 1, 10.99, 8 UNION ALL
	SELECT 20934, 786, 1, 9.99, 7.35 UNION ALL
	SELECT 20934, 786, 2, 9.99, 7.35 UNION ALL
	SELECT 20934, 1554, 1, 6.99, 4.91 UNION ALL
	SELECT 20934, 721, 2, 10.99, 8 UNION ALL
	SELECT 20933, 1554, 1, 6.99, 4.91 UNION ALL
	SELECT 20933, 1554, 1, 5.99, 4.91

DECLARE @Products TABLE(ProductID INT, Product VARCHAR(55), CategoryID INT)

INSERT @Products(ProductID, Product, CategoryID)
	SELECT 1554, 'Woodbridge Chardonnay, 750 ml', 39 UNION ALL
	SELECT 721, 'SHARPE HILL RED SERAPH, 750 ml', 38 UNION ALL
	SELECT 786, 'BUD , 12 oz 12 pack Bottle(s)', 3 UNION ALL
	SELECT 1555, 'Woodbridge Chardonnay, 1.5 L', 39

DECLARE @Results TABLE(ident INT IDENTITY(1,1) PRIMARY KEY, Product VARCHAR(55), Quantity INT)

INSERT @Results(Product, Quantity)
	SELECT 
		P.Product, 
		S.Quantity
	FROM 
		@Sales S 
		INNER JOIN @Products P ON P.ProductID = S.ProductID 

SELECT 
	r.Product,
	r.Quantity 
FROM 
	@Results r
	INNER JOIN (
		SELECT MIN(ident) AS ident, Product FROM @Results GROUP BY Product
		UNION ALL
		SELECT MIN(ident)+1, Product FROM @Results GROUP BY Product) dt ON r.ident = dt.ident AND r.Product = dt.Product
ORDER BY
	r.ident,
	r.Product

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-19 : 00:46:40

Also refer point 2 here
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources