SP to return x number of records with partition on unique value

Question

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:

fld1	fld2	fld3
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

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

And if there are only 7 unique values, what are the 8 rows you are expecting? Please show your desired results. — Dale K
– Dale K, Commented Sep 7, 2022 at 22:17
the full sp is actually building some filtering in that i didn't show in the question so i'm building it with dynamic sql. As far as the question of returning 8 rows when there are 7 unique values is that it's a requirement. Our lab staff wants to select 8 samples every so of from new specimens that have been tested in a time period. Generally, there will always be more than 8 unique values in fld3, but that may not always be the case and they have to test 8. — Steve
– Steve, Commented Sep 7, 2022 at 22:21
1) Given you have managed to simplify the scenario - well done - why not just show us the static SQL? Easier for all. 2) You still have to define to us what the 8th row should look like - else how would we know to write a query? — Dale K
– Dale K, Commented Sep 7, 2022 at 22:23

Dale K · Accepted Answer · 2022-09-07 23:40:05Z

0

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:

fld1	fld2	fld3
4	205	205
9	222	108
3	203	220
5	207	16678
1	198	1480
2	200	1373
6	212	205
7	214	220

edited Sep 7, 2022 at 23:40

answered Sep 7, 2022 at 22:56

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charlieface Over a year ago

You can actually put ROW_NUMBER directly in the ORDER BY weirdly

Dale K Over a year ago

@Charlieface true I keep forgetting that :)

Charlieface · Accepted Answer · 2022-09-07 23:04:28Z

0

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;

edited Sep 7, 2022 at 23:04

answered Sep 7, 2022 at 22:44

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

2 Comments

Dale K Over a year ago

For some reason that doesn't return all the unique values first i.e. when I ran it I didn't get fld3=108.

Charlieface Over a year ago

Might have misunderstood the question, it was a little unclear

Collectives™ on Stack Overflow

SP to return x number of records with partition on unique value

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related