I'm trying to create a stored procedure that will return 8 records, that should all be partitioned by a field to be unique, unless there are not enough unique values. It must return 8 records.

Sample data:

Currently, I have this for the stored procedure (missing a few lines) which works to return 8 records, as long as there are 8 unique values in fld3. The piece I'm stuck on is how to return 8 records in there are only 7 unique values in fld3 as in the data above. I would want 6 unique values in fld 3 and then 2 records that would have the same values in fld 3 but also not one of the previously chosen 6 records. Any suggestions?

CREATE PROCEDURE usp_Get8Records
  --passing in variables here
AS
BEGIN TRY
  SET NOCOUNT ON;
  SET XACT_ABORT ON;
  SELECT TOP 8 fld1, fld2, fld3
  FROM (
    SELECT DISTINCT fld1,
                    fld2,
                    fld3,
                    row_number() OVER (partition by fld3 order by fld1) AS rn
    FROM tblName
    GROUP BY fld1, fld2, fld3
        ) t WHERE rn <= 1'
END TRY
BEGIN CATCH
  --error hander
END CATCH

CodePudding user response：

Use the row_number() function to order your rows a bit like your were doing, but combine with TOP 8.

You can modify the row number ordering to meet any other requirements you might have.

declare @MyTable table (fld1 int, fld2 int, fld3 int);

insert into @MyTable (fld1, fld2 ,fld3)
values
(1, 198, 1480),
(2, 200, 1373),
(3, 203, 220),
(4, 205, 205),
(5, 207, 16678),
(6, 212, 205),
(7, 214, 220),
(8, 221, 16678),
(9, 222, 108),
(10, 223, 205);

select top 8 *
from @MyTable
order by row_number() over (partition by fld3 order by fld2) asc;

Returns:

CodePudding user response：

You just need to order by fld1 and fld2 first before fld3, then take the top 8 rows.

I don't know whether you still need the GROUP BY, so I've left it in.

SELECT TOP (8)
  fld1,
  fld2,
  fld3
FROM tblName
GROUP BY
  fld1,
  fld2,
  fld3
ORDER BY
  fld1,
  fld2,
  fld3;