Using DISTINCT and TOP in the same query

Question

I want to use DISTINCT and TOP in the same query. I tried

SELECT DISTINCT TOP 10 * FROM TableA

but I still have a duplicate personID, so I tought to do:

SELECT DISTINCT (personID) TOP 10 * FROM TableA

But here the syntax is wrong. How do I do it correctly?

What will you do if you want to choose only one `personID` if you have duplicate rows? — John Woo, Mar 18 '13 at 14:36

score 73 · Answer 1 · answered Mar 18 '13 at 14:28

You're using a SELECT * which is pulling in all records. If you want to use a true DISTINCT only list out the column you want to receive distinct values of. If you have multiple columns then all those columns combined make up one distinct record.

SELECT distinct TOP 10 personID 
FROM TableA

Note that without an ORDER BY this will return the first 10 records in no particular order. The results could be different each time you run the query.

score 11 · Answer 2 · answered Mar 18 '13 at 15:51

You seem to want 10 random records for different persons. Try this:

select t.*
from (select t.*,
             row_number() over (partition by personid order by (select NULL)) as seqnum
      from t
     ) t
where seqnum = 1

In general, though, when using top you should also be using an order by to specify what you mean by "top".

score 9 · Answer 3 · edited Jun 15 '17 at 22:23

9

It works simply if you use query like this:

SELECT DISTINCT TOP 2 name FROM [ATTENDANCE] ;

In the above query, name is the column_name and [ATTENDANCE] is the table_name.

You can also use WHERE with this to make filtering conditions.

edited Jun 15 '17 at 22:23

SQL Police

4,127
1
25
54

answered May 25 '17 at 11:35

Arulmouzhi

1,878
17
20

score 4 · Answer 4 · answered Feb 28 '17 at 13:27

4

select distinct personid from tablea sample 10

This works in teradata

answered Feb 28 '17 at 13:27

Pinky Joe

69
10

score 2 · Answer 5 · edited Sep 26 '22 at 09:34

2

Maybe you can do like this:

SELECT TOP 10 t.* FROM (SELECT distinct personID FROM TableA) t

edited Sep 26 '22 at 09:34

Bill Tür stands with Ukraine

3,425
30
38
48

answered Jun 28 '21 at 09:36

Henro Sutrisno Tanjung

440
3
6

score 1 · Answer 6 · answered Mar 26 '21 at 16:02

Few options:

1: SELECT TOP 10 personID FROM (SELECT distinct personID FROM TableA)

2: ;with cte as (SELECT distinct personID FROM TableA) SELECT top 10 personID FROM cte

3: ;with cte as (SELECT personID,row_count=row_number() over (partition by personID order by personID) FROM TableA ) SELECT top 10 personID FROM cte where row_count=1

score 0 · Answer 7 · answered Jun 28 '21 at 09:58

0

I see this is an old question, but I can suggest a more elegant solution:

SELECT personDetails.*
FROM
(
    SELECT      TOP (10)
                personID
                personID
    FROM        TableA
    GROUP BY    personID    --> use this instead of distinct
)   AS          distinctIds

OUTER APPLY
(
    SELECT  TOP (1) *       --> fetch only one result matching personID
    FROM    TableA
    WHERE   TableA.personId = distinctIds.personID
)   AS      personDetails

You can use GROUP BY instead of DISTINCT, and you can use OUTER APPLY with TOP(1) to fetch only one matching result.

answered Jun 28 '21 at 09:58

Yair Maron

1,860
9
22

IMO definitely not more elegant that Matt Busche's solution (which should be the answer). Unless you put it in a fancy font maybe :) – 8forty Jul 18 '21 at 21:31
@8forty , the solution of Matt Busche returns only the personID, while the solution I provided gives back all the fields (select *) – Yair Maron Jul 19 '21 at 07:13

score 0 · Answer 8 · answered Oct 11 '21 at 14:16

The Matt Busche answer is correct based on the post title.
But if, as it seems from your code examples, your problem is to have all the record fields from the distinct top 10 personID, you can use that as a subquery:

SELECT TableA.* 
FROM TableA
INNER JOIN (SELECT DISTINCT TOP 10 personID FROM TableA) AS B
ON TableA.ID = B.ID

Snehal Mankar · Answer 9 · 2019-06-14T12:46:24.863

-1

SELECT DISTINCT ta.personid FROM (SELECT TOP 10 * FROM TableA) ta

ta is object of subquery and using ta object we can distinct the values

edited Jun 14 '19 at 12:46

answered Jun 13 '19 at 10:31

Snehal Mankar

1
1

2

Hi welcome to Stackoverflow, can you add a brief description of the code you posted here? Just posting code is usually not appreciated at SO. – Stephan Hogenboom Jun 13 '19 at 10:59
1

add details/description related to your answer. check https://stackoverflow.com/help/how-to-answer – Abhijeet Jun 13 '19 at 12:19
This is not a good solution- the subquery (with top 10) might give you 10 rows of the same personID, which will result in having 1 row in the resultset – Yair Maron Jun 28 '21 at 10:07

score -3 · Answer 10 · answered Mar 18 '13 at 15:23

-3

i fixed it i did

select distinct  personid from (SELECT  TOP 10 * FROM TableA)

answered Mar 18 '13 at 15:23

user1187282

1,137
4
14
23

Without an order by results may vary. Even a table with PK is not guaranteed to return the same order without an order by. – paparazzo Mar 18 '13 at 15:29
1

if you have the same personid in your top 10 results then this won't work, you would only get 9 results. It's as simple as `SELECT distinct TOP 10 personID FROM TableA` – Matt Busche Mar 18 '13 at 17:18

score -3 · Answer 11 · answered Oct 02 '17 at 18:23

-3

If the goal is to select the top 1 record of every personID, then use

select * from TableA group by personid

Since you are doing a "group by", it will return every column, but will ignore (not display) any additional rows with the same personId

answered Oct 02 '17 at 18:23

Vette

511
5
10

This will not work in sql-server, it will result with an error – Yair Maron Jun 28 '21 at 10:04

Using DISTINCT and TOP in the same query

11 Answers11

Linked