CODEWITHSUNDEEP
sql
user7333846
user7333846

Reputation: 81

How can I select one value from list

I would like to select one record if there are many status. example, if there is the same user and status A, B, C, select only record 'A'. If there is the same user and status B, C, choose B, if there is only one status, bring that one.

=======================
User    Name    Status
=======================
123 |   Alice   | B
123 |   Alice   | A
123 |   Alice   | C
124 |   Mark    | C
125 |   Jonh    | B
126 |   Pate    | B
126 |   Pate    | C

expected RESULT

=======================
User    Name    Status
=======================
123 |   Alice   | A
124 |   Mark    | C
125 |   Jonh    | B
126 |   Pate    | B

Could anyone help to suggest? Thank you

Upvotes: 0

Views: 1272

Answers (2)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

group by does what you need in that case, and it's a bit easier than analytical functions:

select user, name, min(status) as status
  from yourtable
  group by user, name;

Upvotes: 2

Atif
Atif

Reputation: 2210

Use row_number to select first record:

SELECT X.* FROM
 (
SELECT A.*, 
ROW_NUMBER() OVER(PARTITION BY USER, NAME ORDER BY STATUS) RN FROM
TABLE1
) X WHERE RN = 1;

Upvotes: 0

Related Questions