Getting distinct result with Oracle SQL

Question

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 :-)

And what should id have? Do you want the min(id) too? The id from the row with the min(name)? Something else? — Justin Cave
– Justin Cave, Commented Aug 11, 2015 at 15:28

sstan · Accepted Answer · 2015-08-11 15:29:49Z

4

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

answered Aug 11, 2015 at 15:29

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

N West · Accepted Answer · 2015-08-11 15:28:02Z

1

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.

answered Aug 11, 2015 at 15:28

N West

6,79428 silver badges40 bronze badges

Collectives™ on Stack Overflow

Getting distinct result with Oracle SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related