mkell mkell - 1 month ago 5
SQL Question

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.

Answer

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