How to do this... top 2 inner join

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)

How to do this... top 2 inner join

Author

Topic

dave2118
Starting Member

8 Posts

Posted - 2011-01-24 : 11:54:51

Say here's my table:

Item
----
ItemID
ItemTypeID
ItemDate
ItemDescription

ItemType
--------
ItemTypeID
ItemTypeDescription

Insert into Item Values (1, 1, '1/24/2011', 'Blah')

Insert into Item Values (2, 1, '1/22/2011', 'Blah')

Insert into Item Values (3, 1, '1/20/2011', 'Blah')

Insert into Item Values (4, 1, '1/17/2011', 'Blah')

Insert into Item Values (5, 1, '1/14/2011', 'Blah')

Insert into Item Values (6, 2, '1/2/2011', 'Blah')

Insert into Item Values (7, 2, '1/22/2011', 'Blah')

Insert into Item Values (8, 2, '1/14/2011', 'Blah')

Insert into Item Values (9, 3, '1/20/2011', 'Blah')

Insert into Item Values (10, 5, '1/17/2011', 'Blah')

Insert into Item Values (11, 5, '1/13/2011', 'Blah')

Insert into Item Values (12, 5, '1/10/2011', 'Blah')

I want to get the top two items, grouped by ItemType. So my result would be like

1
6
2
7
9
10
11

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 12:00:46

[code]
SELECT ItemID
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ItemTypeID ORDER BY ItemID) AS rn, ItemID
FROM Item
)t
WHERE rn < = 2
ORDER BY rn,itemID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dave2118
Starting Member

8 Posts

Posted - 2011-01-24 : 12:06:50

Thank You!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 12:16:09

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources