Single row selection without a unique key

Question

I have a table like this

Fld1 Fld2 Fld3 ------------ 0 1234 ABC 0 1235 DEF 1 1236 GHI 2 1236 JKL 3 1236 MNO 4 1237 PQR 5 1237 STU 6 1237 VWX 

Note that no column is unique. There may be many rows with Fld1 = 0, but for all other values, Fld1 will be unique, and there may be many rows with the same value for Fld2.

I need to select one row for each Fld2 value with the highest value in Fld 1. Thus, a result based on the above data will be

 Fld1 Fld2 Fl4 ------------------ 0 1234 ABC 0 1235 DEF 3 1236 MNO 6 1237 VWX 

Answer 1

An alternative to GROUP BY would be to use a windowing function, such as row_number() , to get the result. This function creates a unique number, breaking your data into Fld2 and ordering it Fld1 desc :

 select Fld1, Fld2, Fld3 from ( select Fld1, Fld2, Fld3, rn = row_number() over(partition by fld2 order by fld1 desc) from yourtable ) d where rn = 1; 

See SQL Fiddle with Demo . Sometimes using row_number will be easier if you have additional columns that are unique. This gives the result:

 | Fld1 | Fld2 | Fld3 | |------|------|------| | 0 | 1234 | ABC | | 0 | 1235 | DEF | | 3 | 1236 | MNO | | 6 | 1237 | VWX | 

Answer 2

Use group by :

 select max(fld1), fld2 from table t group by fld2; 

Answer 3

a) Get the first result, which has only two columns, as Gordon's answer.
b) Use it to query the remaining columns.

 SELECT r.* FROM table r INNER JOIN (select max(fld1), fld2 from table t group by fld2) s ON (r.fld1=s.fld1 AND r.fld2=s.fld2)