Top 2 per group - 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 2008 Forums

Transact-SQL (2008)

Top 2 per group

Author

Topic

matrixrep
Starting Member

30 Posts

Posted - 2014-05-26 : 11:03:17

I have the following table

Shop------Product-----Color--------Type
---------------------------------------
001-------12345-------White--------1
001-------23456-------White--------1
001-------34567-------White--------1
001-------45678-------Blue---------1
001-------56789-------Blue---------1
001-------67890-------Blue---------1
001-------78901-------White--------2
001-------89012-------White--------2
001-------90123-------White--------2
001-------01234-------Blue---------2
001-------12356-------Blue---------2
001-------23456-------Blue---------2
002 .....

All product have a unique number. I can have hundred of products per color and more than 2 color per shop.
I have only two type per shop.

I need a select statement that will choose the first two product per color for each type and each shop.

Result table
Shop------Product-----Color--------Type
---------------------------------------
001-------12345-------White--------1
001-------23456-------White--------1
001-------45678-------Blue---------1
001-------56789-------Blue---------1
001-------78901-------White--------2
001-------89012-------White--------2
001-------01234-------Blue---------2
001-------12356-------Blue---------2
002 .....

Thanks in advance for your cooperation.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-26 : 15:00:19

Here's one (untested) way:


;with grp (Shop, Color, [Type])
as
(
       select Shop, Color, [Type]
       from   <yourTable>
       group by Shop, Color, [Type]
)
select g.Shop, g.Color, ca.product, g.[Type]
from   grp as g
cross apply (
       select top 2 t.product
       from   <yourTable> t
       where  t.shop = g.shop
       and    t.color = g.color
       and    t.[type] = g.[type]
       order by product
       ) ca

Be One with the Optimizer
TG

matrixrep
Starting Member

30 Posts

Posted - 2014-05-26 : 16:12:42

Thanks a lot TG

it is doing what i was looking for. :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-06-17 : 03:01:38

Also refer this http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-18 : 01:32:23

You can also use Row_Number to do this..


CREATE TABLE #temp(Shop int,Product INT,Color varchar(100),[Type] int)
INSERT INTO #temp
SELECT 001,12345,'White',1 UNION ALL
SELECT 001,23456,'White',1 UNION ALL
SELECT 001,34567,'White',1 UNION ALL
SELECT 001,45678,'Blue',1 UNION ALL
SELECT 001,56789,'Blue',1 UNION ALL
SELECT 001,67890,'Blue',1 UNION ALL
SELECT 001,78901,'White',2 UNION ALL
SELECT 001,89012,'White',2 UNION ALL
SELECT 001,90123,'White',2 UNION ALL
SELECT 001,01234,'Blue',2 UNION ALL
SELECT 001,12356,'Blue',2 UNION ALL
SELECT 001,23456,'Blue',2

SELECT	         Shop
		,Product
		,Color
		,[Type] FROM 
(SELECT  *
		,RowNum = ROW_NUMBER() OVER (PARTITION BY [Type],color ORDER BY (SELECT 1)
) FROM  #temp) a WHERE a.RowNum IN (1,2)

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources