CODEWITHSUNDEEP
sqlsql-server
Myles McDonnell
Myles McDonnell

Reputation: 13335

Selecting single row with no unique key

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

Upvotes: 8

Views: 418

Answers (3)

Alexander
Alexander

Reputation: 20244

a) Get the first result that has only two columns, as per Gordons answer.
b) Use it to query for 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)

Upvotes: 0

Taryn
Taryn

Reputation: 247880

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 |

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1271161

Use group by:

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

Upvotes: 1

Related Questions