0

Below you can find my table with values (there are no constraints on my table):

SELECT DISTINCT * FROM khalil;

outputs:

ID VALUE
-- -------
 1 yassine
 1 khalil
 2 amine

I need to get the first row when I have duplicate values.

I have two rows with id = 1 so, in this case, I need that the first one, which is id = 1 and value = 'yassine'

MT0
  • 143,790
  • 11
  • 59
  • 117
kdiouri1994
  • 13
  • 1
  • 7
  • 1
    There is no *first* record unless you have a column to order the results... – GMB Jan 06 '20 at 14:34
  • there is a solution using oracle to create temporary column to order the results? – kdiouri1994 Jan 06 '20 at 14:37
  • 1
    Oracle can only use existing columns to predictably order results. Otherwise the order in which rows are returned are not guaranteed. If you want to guarantee that yassine is the row returned with id = 1, then you need to add another sequence column that gives a distinct value for each id and order by that. – Zynon Putney II Jan 06 '20 at 14:50

2 Answers2

1
SELECT * FROM khalil
WHERE ROWID IN (SELECT MIN(ROWID) FROM khalil GROUP BY id)
ORDER BY id

This will return the first row for each id.

nunoq
  • 550
  • 2
  • 7
0

If you don't really care which value you'll get (unless there's something you can use to distinguish values), aggregates - such as min or max - can help:

SQL> select id,
  2         max(value) value
  3  from khalil
  4  group by id
  5  order by id;

        ID VALUE
---------- --------------------
         1 yassine
         2 amine

SQL>

Alternatively, using analytic functions (such as row_number, which lets you sort values), you'd do it as follows:

SQL> with temp as
  2    (select id,
  3            value,
  4            row_number() over (partition by id order by value desc) rn
  5     from khalil
  6    )
  7  select id,
  8         value
  9  from temp
 10  where rn = 1
 11  order by id;

        ID VALUE
---------- --------------------
         1 yassine
         2 amine

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57