CODEWITHSUNDEEP
sql-server
Tony Stark
Tony Stark

Reputation: 156

select top 2 rows in each category

Is that possible to select two rows in each category

I have a table like below 

 DECLARE @temptable TABLE 
 ( id int IDENTITY(1,1) 
 ,name nvarchar(20) NULL 
 ,category int NOT NULL)

INSERT INTO @temptable ( name ,category)
VALUES 
( 'nikhil1' ,1)
, ('sumesh1' ,2)
, ('sumesh2' ,2)
, ('nikhil2' ,1)
, ('nikhil3',1)
, ('sumesh3' ,2)


SELECT *
FROM @temptable t
ORDER BY t.id ASC

i want the result as below

| id | Name  | category|
|----------------------|
| 1  |nikhil1|  1      |
| 2  |nikhil2|  1      |
| 3  |sumesh1|  2      |
| 4  |sumesh2|  2      |

All thoughts are appreciated!

Upvotes: 1

Views: 52

Answers (2)

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

An alternative to a CTE is a derived table, which will effectively do the same thing...

SELECT id, name, category
FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY name) AS rn
        FROM @temptable
     ) tbl
WHERE rn<=2

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

;WITH CategoriesCTE AS
(
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY category ORDER BY name) AS rn
    FROM   @temptable
)
SELECT *
FROM CategoriesCTE t
WHERE rn<=2

A bit of background on that: We are defining a CTE which will have an extra column that for each category will give an incremental number on its items. That number will be zeroed in each change of category. This is enforced by the PARTITION BY clause in the ROW_NUMBER. Then we are filtering the results to get all products which got the first two numbers in each category.

Upvotes: 4

Related Questions