Using DISTINCT and TOP at the same time

I want to use distinct & top in the same time , i did

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 the syntax is wrong , so i wonder if there is any solution

thanks,

11 Answers

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.

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".

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.

select distinct personid from tablea sample 10

This works in teradata

Maybe you can do like this:

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

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

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.

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 

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

i fixed it i did

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

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