SQL select Top 10 records for unique combination of two columns

Transact-SQL
1

I am trying to capture the top 10 records for each date and company combination. The data listed below a very,very small sample. The dataset is over 100,000 records spanning over two years worth of dates There are 17 different companies information. I am using SSMS

9/20/2023  AARP
9/27/2023  AARP
9/13/2023  AARP
9/20/2023  AARP
9/20/2023  AARP
9/27/2023  PRAA
9/13/2023  PRAA
9/20/2023  PRAA
9/27/2023  PRAA
9/13/2023  PRAA
9/20/2023  PRAA
9/27/2023  PRAA
9/13/2023  XYZZ
9/20/2023  XYZZ
9/27/2023  XYZZ
9/13/2023  XYZZ
9/20/2023  XYZZ

I want to obtain the top 10 distinct records for each combination of date and company. I've tried tons ot the suggested codes out there but none of the will provide me with the results I need. Here is a sample of the output needed.

9/20/2023  AARP
9/27/2023  AARP
9/27/2023  PRAA
9/13/2023  PRAA
9/13/2023  XYZZ
9/20/2023  XYZZ

I posted this question on another forum,stackoverflow-questions-78233367-sql-select-top-10-records-for-each-two-columns-combination They closed the ticket without answering the question.

2

It doesn't seem clear to me what your goal is.
In the example you gave, why do you exclude some dates?
For example, for the AARP company there are records:
9/20/2023 AARP
9/27/2023 AARP
9/13/2023 AARP
9/20/2023 AARP
9/20/2023 AARP
and the different combinations for different dates are:
9/13/2023 AARP
9/20/2023 AARP
9/27/2023 AARP
Why only records appear in your output:
9/20/2023 AARP
9/27/2023 AARP
?
Why exclude 9/13/2023 AARP
?

3

Maybe?:


DROP TABLE IF EXISTS #data;
CREATE TABLE #data ( date date NOT NULL, company varchar(30) NOT NULL );
INSERT INTO #data VALUES
    ('9/20/2023', 'AARP'), ('9/27/2023', 'AARP'), ('9/13/2023', 'AARP'), ('9/20/2023', 'AARP'), ('9/20/2023', 'AARP'),
    ('9/27/2023', 'PRAA'), ('9/13/2023', 'PRAA'), ('9/20/2023', 'PRAA'), ('9/27/2023', 'PRAA'), ('9/13/2023', 'PRAA'), 
        ('9/20/2023', 'PRAA'), ('9/27/2023', 'PRAA'),
    ('9/13/2023', 'XYZZ'), ('9/20/2023', 'XYZZ'), ('9/27/2023', 'XYZZ'), ('9/13/2023', 'XYZZ'), ('9/20/2023', 'XYZZ')

;WITH cte_uniques AS(
    SELECT date, company, ROW_NUMBER() OVER(PARTITION BY company, date ORDER BY company, date) AS row_num
    FROM #data
    GROUP BY date, company
)
SELECT *
FROM cte_uniques
WHERE row_num <= 10
ORDER BY company, date
4

Here is some additional explanations. I'm new to posting on user forums some I may forget things in my problem description.
This is just a very small sample. Within the true dataset, there could be 20 occurences of the same date/company combination. I only want one occurrence for each combination. Then I would like the top 10 records from that subset

5

@ScottPletcher - this would work if I only used those specific combinations. I need the query to be more dynamic as I am dealing with over 100,000 records and potentially 50 differents dates and 17 different companies. Each date/company combination could have multiple occurrences.

6

This doesn't answer my question.
For the AARP company there are 3 different combinations for date/company.
You said you only wanted 1 occurrence of each combination, so again, why do you only list 2? Why do you exclude the combination 9/13/2023 AARP?

7

I just created sample data to allow the query to be run. You can use the query on your original table and it should handle all companies and dates. If the rows are wide, you'll want an index on ( company, name ) to make the query run a lot faster.

8

@gdl - in the production version of the QGL query needed, the 9/13/2023 AARP record would be included. If you would like I can provide a larger raw dataset. Each date/company may have only one record or multiple records associated with the combination. The output should only include.one unique record for each combination

There won't be the same number of dates for each company. Company AARP may have 50 dates, where XYZZ may only have 20 dates.

9

From what you say, it seems to me that @ScottPletcher's solution is fine, only I would write row_number like this:

row_number() over (partition by company order by company, [date]) AS row_num

So, to give a different example, consider this solution:

declare @T as table
(	[date] date
	, company varchar(4)
)
insert into @T ([date], company) values
  ('20230901', 'AARP')
, ('20230902', 'AARP')
, ('20230903', 'AARP')
, ('20230904', 'AARP')
, ('20230905', 'AARP')
, ('20230906', 'AARP')
, ('20230910', 'AARP')
, ('20230908', 'AARP')
, ('20230909', 'AARP')
, ('20230910', 'AARP')
, ('20230911', 'AARP')
, ('20230912', 'AARP')
, ('20230902', 'AARP')
, ('20230914', 'AARP')
, ('20230915', 'AARP')
, ('20230927', 'PRAA')
, ('20230913', 'PRAA')
, ('20230920', 'PRAA')
, ('20230927', 'PRAA')
, ('20230913', 'PRAA')
, ('20230920', 'PRAA')
, ('20230927', 'PRAA')
, ('20230915', 'XYZZ')
, ('20230916', 'XYZZ')
, ('20230921', 'XYZZ')
, ('20230925', 'XYZZ')
, ('20230926', 'XYZZ')
, ('20230930', 'XYZZ')
, ('20231004', 'XYZZ')
, ('20231008', 'XYZZ')
, ('20231012', 'XYZZ')
, ('20231015', 'XYZZ')
, ('20231011', 'XYZZ')
, ('20231012', 'XYZZ')
--
;with cte as
(
	select
		row_number() over (partition by company order by company, [date]) AS rn
		, [date]
		, company
	from @T
	group by [date], company
)
select date, company
from cte
where rn <= 10
order by company, [date]