SQL - Select unique rows from a group of results

Question

Hi all.

I have wrecked my brain on this problem for quite some time. I've also reviewed other questions but was unsuccessful.

The problem I have is, I have a list of results/table that has multiple rows with columns

| REGISTRATION | ID     | DATE            | UNITTYPE

| 005DTHGP        | 172   | 2007-09-11  | MBio
| 005DTHGP        | 1966 | 2006-09-12  | Tracker
| 013DTHGP        | 2281 | 2006-11-01  | Tracker
| 013DTHGP        | 2712 | 2008-05-30  | MBio
| 017DTNGP        | 2404 | 2006-10-20  | Tracker
| 017DTNGP        | 508   | 2007-11-10  | MBio

I am trying to select rows with unique REGISTRATIONS and where the DATE is max (the latest). The IDs are not proportional to the DATE, meaning the ID could be a low value yet the DATE is higher than the other matching row and vise-versa. Therefore I can't use MAX() on both the DATE and ID and grouping just doesn't seem to work.

The results I want are as follows;

| REGISTRATION | ID     | DATE            | UNITTYPE

| 005DTHGP        | 172   | 2007-09-11  | MBio
| 013DTHGP        | 2712 | 2008-05-30  | MBio
| 017DTNGP        | 508   | 2007-11-10  | MBio

PLEASE HELP!!!?!?!?!?!?!?

Answer 1

This should work in MySQL:

SELECT registration, id, date, unittype FROM 
  (SELECT registration AS temp_reg, MAX(date) as temp_date 
  FROM table_name GROUP BY registration) AS temp_table
WHERE registration=temp_reg and date=temp_date

The idea is to use a subquery in a FROM clause which throws up a single row containing the correct date and registration (the fields subjected to a group); then use the correct date and registration in a WHERE clause to fetch the other fields of the same row.

Answer 2

TSQL:

declare @R table
(
Registration varchar(16),
ID int,
Date datetime,
UnitType varchar(16)
)

insert into @R values ('A','1','20090824','A')
insert into @R values ('A','2','20090825','B')

select R.Registration,R.ID,R.UnitType,R.Date from @R R
inner join
(select Registration,Max(Date) as Date from @R group by Registration) M
on R.Registration = M.Registration and R.Date = M.Date

This can be inefficient if you have thousands of rows in your table depending upon how the query is executed (i.e. if it is a rowscan and then a select per row).

Answer 3

You want embedded queries, which not all SQLs support. In t-sql you'd have something like

select  r.registration, r.recent, t.id, t.unittype
from    ( 
    select registration, max([date]) recent
    from @tmp 
    group by 
     registration
    ) r
left outer join 
    @tmp t 
on  r.recent = t.[date]
and r.registration = t.registration

Answer 4

In PostgreSQL, and assuming your data is indexed so that a sort isn't needed (or there are so few rows you don't mind a sort):

select distinct on (registration), * from whatever order by registration,"date" desc;

Taking each row in registration and descending date order, you will get the latest date for each registration first. DISTINCT throws away the duplicate registrations that follow.

Answer 5

select registration,ID,date,unittype
from your_table
where (registration, date) IN (select registration,max(date)
                            from your_table
                            group by registration)