Using DISTINCT and TOP at the same time

Question

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,

Answer 1

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.

Answer 2

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