How to only display the first 2 rows of a group with MS SQL Server
- We don't want all the rows for a group, only the first two if there are to or more, or only one if there is only 1 row for that group.
Data used
USE adventureworks;
go
SELECT ProductID,StartDate,EndDate,StandardCost
FROM production.productcosthistory
WHERE productid between 715 and 720;
go
go
SELECT ProductID,StartDate,EndDate,StandardCost
FROM production.productcosthistory
WHERE productid between 715 and 720;
go
ProductID StartDate EndDate StandardCost ----------- ----------------------- ----------------------- --------------------- 715 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 31.7244 715 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 29.0807 715 2003-07-01 00:00:00.000 NULL 38.4923 716 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 31.7244 716 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 29.0807 716 2003-07-01 00:00:00.000 NULL 38.4923 717 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 747.9682 717 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 722.2568 717 2003-07-01 00:00:00.000 2004-06-30 00:00:00.000 868.6342 717 2004-07-01 00:00:00.000 2005-06-30 00:00:00.000 256.36 717 2005-07-01 00:00:00.000 2006-06-30 00:00:00.000 856.98 717 2006-07-01 00:00:00.000 2007-06-30 00:00:00.000 142.00 717 2007-07-01 00:00:00.000 NULL 256.00 718 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 747.9682 718 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 722.2568 718 2003-07-01 00:00:00.000 NULL 868.6342 719 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 747.9682 719 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 722.2568 719 2003-07-01 00:00:00.000 NULL 868.6342 720 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 747.9682 720 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 722.2568 720 2003-07-01 00:00:00.000 NULL 868.6342 (22 row(s) affected)
Getting only the first two rows for each product, Part 1
- The simplest method is to count the rows by product id.
- Please do not use cursors or temporary tables.
- You cannot use the row number directly in the selection criteria, because of the order of evaluation.
- You must use inline views for selecting by row numbers.
SELECT * FROM (
SELECT ProductID, StartDate, EndDate, StandardCost,
[ROW Counter] = ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductId,StartDate)
FROM Production.ProductCostHistory
WHERE productid between 715 and 720
) ABC
WHERE [ROW Counter] between 1 and 2;
SELECT ProductID, StartDate, EndDate, StandardCost,
[ROW Counter] = ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductId,StartDate)
FROM Production.ProductCostHistory
WHERE productid between 715 and 720
) ABC
WHERE [ROW Counter] between 1 and 2;
ProductID StartDate EndDate StandardCost Row Counter ----------- ----------------------- ----------------------- --------------------- -------------------- 715 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 31.7244 1 715 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 29.0807 2 716 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 31.7244 1 716 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 29.0807 2 717 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 747.9682 1 717 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 722.2568 2 718 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 747.9682 1 718 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 722.2568 2 719 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 747.9682 1 719 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 722.2568 2 720 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 747.9682 1 720 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 722.2568 2 (12 row(s) affected)
ROW_NUMBER()
- ORDER BY ProductId,StartDate: Number the rows in order by productid and by startdate.
- PARTITION BY ProductID: Reset the row number to 1 whenever the productid changes.
- For more information on inline views, including samples and explanations see: Inline views with MS SQL Server
Getting only the first two rows for each product, Part 2
- I don't want either cost or the row number.
SELECT ProductID, StartDate, EndDate
FROM (
SELECT ProductID, StartDate, EndDate, StandardCost,
[ROW Counter] = ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductId,StartDate)
FROM Production.ProductCostHistory
WHERE productid between 715 and 720
) ABC
WHERE [ROW Counter] between 1 and 2;
FROM (
SELECT ProductID, StartDate, EndDate, StandardCost,
[ROW Counter] = ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductId,StartDate)
FROM Production.ProductCostHistory
WHERE productid between 715 and 720
) ABC
WHERE [ROW Counter] between 1 and 2;
ProductID StartDate EndDate ----------- ----------------------- ----------------------- 715 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 715 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 716 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 716 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 717 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 717 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 718 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 718 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 719 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 719 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 720 2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 720 2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 (12 row(s) affected)
- You do not need to display the row number in the outside query, or any other column.