Oracle Distinct on one column

Question

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 ?

Answer 1

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.