CODEWITHSUNDEEP
sqloracle-database
frgtv10
frgtv10

Reputation: 5450

Getting distinct result with Oracle SQL

I have the following data structure

ID  | REFID | NAME
1   | 100   | A
2   | 101   | B
3   | 101   | C

With

SELECT DISTINCT REFID, ID, NAME
FROM my_table
ORDER BY ID

I would like to have the following result:

1   | 100   | A
2   | 101   | B

Colum NAME and ID should contain the MIN or FIRST value.

But actually I get stuck at using MIN/FIRST here.

I welcome every tipps :-)

Upvotes: 0

Views: 37

Answers (2)

sstan
sstan

Reputation: 36483

select id,
       refid,
       name
  from (select id, 
               refid,
               name,
               row_number() over(partition by refid order by name) as rn
          from my_table)
 where rn = 1
 order by id

Upvotes: 4

N West
N West

Reputation: 6819

You can use a subquery to do this.

WITH Q AS 
( SELECT MIN(NAME) AS NAME, REFID FROM T GROUP BY REFID )
SELECT T.ID, T.REFID, T.NAME
FROM T
JOIN Q 
  ON (T.NAME = Q.NAME)

Also, note that SQL tables have no order. So there's no "First" value.

Upvotes: 1

Related Questions