asked on

SQL query

Hi All,

From table TEMP_Table there are multiple rows with the same name in the NAME column and there is distinct ID for them.I want to fetch one NAME (unique or distinct) and any one ID. Please advise how to write MS SQL query for above scenario.

TEMP_Table data:

NAME ID
----------------------------------------------------------------
A        1049
B        1050
C        1051
C        1052
C        1053
C        1054
C        1055
C        1056
D      1057
D        1058

Required output:
NAME ID
----------------------
A 1049
B 1050
C Any ID
D Any ID

Thanks in Advance.

Jim Horn

This will return the first ID

SELECT name, Min(id)
FROM TEMP_Table
GROUP BY name

Open in new window

This will pick a random ID every time

;with a as (
   -- Generate random numbers
   SELECT name, id, ABS(CHECKSUM(NewId())) % 1000 as random_number 
   FROM TEMP_Table), 
b as (
   -- Rank order those random numbers
   SELECT name, id, ROW_NUMBER() OVER (PARTITION BY name ORDER BY random_number) as rank_order FROM a) 
-- Now pick the top one
SELECT name, id FROM b WHERE rank_order = 1

Open in new window

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

Good solution Jim.
I don't know why I used a complexed one.
Better, I think I know. Just gave similar solution to another question and got addicted to this one. :)

Vikas Garg

Hi,

If you want random id from the ID column I think this one liner will do for you..

SELECT Name, MIN(ID) ID FROM TEMP_Table GROUP BY NAME

OR

SELECT Name, MAX(ID) ID FROM TEMP_Table GROUP BY NAME

Open in new window

Vitor Montalvão

So, Vikas, in what your solution is different from Jim's?

SQLServer DBA

ASKER