Selecting single row with no unique key

Question

I have a table like so

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 neither 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 a single row for each value of Fld2 with the highest value in Fld 1. So the result based on the above data would 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 like row_number() to get the result. This function creates a unique number by partitioning your data by Fld2 and ordering it by 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. There are times using row_number will be easier when you have additional columns that are unique. This gives a 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;