Using TOP and WHERE in SQL

Question

Is there a way to use TOP and WHERE multiple times? similar as a for loop to create a table?

I am using the following query to create a table that contains the top 26 records where the value of the column [code] is 11:

SELECT TOP 26 [date]
  ,[group]
  ,[code]
  ,[pol]
  ,[relation]
FROM [database].[table1] WHERE group in ('A','B','C',...,'Z') and code = '11'

The problem with this query is that I get 26 records with the value of the column [group] equal to A. This happens because there are thousands of records that meet that criterion.

Ideally, I would like the top 1 of each group (A to Z) with the value of code 11. I could achieve that by running the query above 26 times using TOP 1 and a different value of group, but this is impractical.

Is there any way to run this query multiple times to get the desired table?

Thanks in advance!

Does this answer your question? [Get top n row per group](https://stackoverflow.com/questions/6841605/) — Stu, Nov 10 '22 at 18:28

griv · Accepted Answer · 2022-11-10T18:30:48.163

2

You can use a CTE to assign a ROW_NUMBER(), then only return rows WHERE ROW_NUMBER() = 1. You may need to adjust the PARTITION/ORDER BY depending on your data and what you're expecting your result set to be.

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY [group], code ORDER BY code ASC) AS rn
   FROM table1
)
SELECT *
FROM cte
WHERE rn = 1 AND code = 11

Demo here.

edited Nov 10 '22 at 18:30

answered Nov 10 '22 at 18:25

griv

2,098
2
12
15

GMB · Answer 2 · 2022-11-10T18:35:21.443

Assuming that your database support WITH TIES, then no need for a subquery. You can just order by with row_number:

SELECT TOP 1 WITH TIES
   [date]
  ,[group]
  ,[code]
  ,[pol]
  ,[relation]
FROM [database].[table1]
WHERE group in ('A','B','C',...,'Z') and code = '11'
ORDER BY ROW_NUMBER() OVER(PARTITION BY [group] ORDER BY [date] desc)

You did not tell which column should be used to identify the top row per group, so I used a descending sort on the date. For each group, ROW_NUMBER assigns 1 to the row with the latest date ; TOP 1 WITH TIES then selects all such rows.

Using TOP and WHERE in SQL

2 Answers2