Oracle Distinct on one column

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 tORDER 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 tGROUP BY itemORDER BY item

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

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 qtyFROM   table_nameGROUP 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 qtyFROM   table_nameGROUP BY item

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

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

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

select item, max(loc) as loc, . . .from tgroup 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     ) twhere seqnum = 1;