CODEWITHSUNDEEP
sqloracle-databasedistinct
Imran Hemani
Imran Hemani

Reputation: 629

Oracle Distinct on one column

I have the table structure in following way:

ITEM  LOC  STATUS  QTY
001   RS1    A     10
001   JS1    I     20
110   RS1    A     4

I want to retrieve the results based on distinct ITEM only, regardless of that the values come with that

So result should be 

ITEM   LOC   STATUS   QTY
001    JS1     A      10
110    RS1     A       4

How to do that ?

Upvotes: 6

Views: 28176

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 221210

This is a special case of the Top N per category problem in SQL, where N = 1, in case of which some simpler solutions may apply:

A standard SQL solution

The existing answers suggest using row_number() in a derived table, which obviously works. But as I have explained in this article, using first_value() would be more straightforward in most cases. Your result could be produced by this query:

SELECT DISTINCT
  item,
  first_value (loc)    OVER (PARTITION BY item ORDER BY qty) loc,
  first_value (status) OVER (PARTITION BY item ORDER BY qty) status,
  first_value (qty)    OVER (PARTITION BY item ORDER BY qty) qty,
FROM t
ORDER BY item

This is assuming you want ordering by QTY. Replace the ORDER BY clause if some other ordering is desired.

This approach works on all RDBMS

An Oracle specific solution

In Oracle, you can use the vendor specific FIRST aggregate function, which uses the following syntax

SELECT
  item,
  MAX (loc)    KEEP (DENSE_RANK FIRST ORDER BY qty) loc,
  MAX (status) KEEP (DENSE_RANK FIRST ORDER BY qty) status,
  MAX (qty)    KEEP (DENSE_RANK FIRST ORDER BY qty) qty,
FROM t
GROUP BY item
ORDER BY item

I've seen this perform better than the window function based approach, but do check on your side.

Upvotes: 8

MT0
MT0

Reputation: 168623

If you just want the minimum value per item for each column (which is what your expected results show) then:

SELECT ITEM,
       MIN( LOC ) AS LOC,
       MIN( status ) AS status,
       MIN( qty ) AS qty
FROM   table_name
GROUP BY item

If you want the value for an item that are minimum against a particular order then:

SELECT ITEM,
       MIN( LOC )    KEEP ( DENSE_RANK FIRST ORDER BY your_criteria ) AS LOC,
       MIN( status ) KEEP ( DENSE_RANK FIRST ORDER BY your_criteria ) AS status,
       MIN( qty )    KEEP ( DENSE_RANK FIRST ORDER BY your_criteria ) AS qty
FROM   table_name
GROUP BY item

Or if you want to to be all from the same row then:

SELECT item, loc, status, qty
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY item ORDER BY your_criteria ) AS RN
  FROM   table_name t
)
WHERE  RN = 1;

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270873

One method is aggregation, if the values don't need to come from the same row:

select item, max(loc) as loc, . . .
from t
group by item;

If you want all the values from the same row, use row_number():

select t.*
from (select t.*, row_number() over (partition by item order by item) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 4

Related Questions