CODEWITHSUNDEEP
sqlsql-server
mkell
mkell

Reputation: 731

Sql Query to return 3 rows for every entry with the same value in column?

OK, I'm a little outta practice on SQL Queries here, I have a table with thousands of entries. Each row has a unique Id but there is a column named EquipmentId which is not unique and would be present in several rows. I want to return 3 rows for every EquipmentId and if there is less the than 3 entries for an EquipmentID I want those too. ..... make sense ? thanks in advance.

Upvotes: 1

Views: 759

Answers (2)

anjali
anjali

Reputation: 84

Using subqueries you can do it like this:

SELECT * 
FROM
    (SELECT *, Rank() 
     OVER 
         (PARTITION BY equipmentid
          ORDER BY ID) Rank
     FROM stack) AS a 
WHERE
    rn <= 3

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Use ROW_NUMBER() + CTE

;WITH CTE AS(
     SELECT *,
            ROW_NUMBER() OVER ( PARTITION BY EquipmentId  ORDER BY ID ) RN
     FROM TableName
)
SELECT * 
FROM CTE
WHERE RN <= 3 
ORDER BY EquipmentId

Upvotes: 2

Related Questions