three highest and lowest colum values per group

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)

three highest and lowest colum values per group

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-02 : 08:36:14

roland writes "Hello,

from the following Product-table

ProductId
MarketId
CustomerId
Type
Price
...

i would like to get the three highest and lowest prices
for each group of ProductId,MarketId & Type.

How could i do that (without a stored Proc.)???

Many thanks in advance
roland"

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-02 : 10:28:43

SELECT ProductID, MarketID, Type
FROM dbo.Products
GROUP BY ProductID, MarketID, Type

Before I reinvent the invented, read this thread.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-02 : 23:41:58

>> without a stored Proc
Well whatever you do in a stored proc you could do in the client but it would be better in a sp.
The sp should contain something like

select *
from Product p
where Price in (select top 3 price from Product p1 where p.ProductId = p1.ProductId and p.MarketId = p1.MarketId and p.Type = p1.Type order by Price desc)
or
Price in (select top 3 price from Product p1 where p.ProductId = p1.ProductId and p.MarketId = p1.MarketId and p.Type = p1.Type order by Price)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources