Find the max record per group, with additional fields

Question

I have one table named tgps, which has six fields and data

Model   ftype   serial  Date        latitude        longitude
Car     B       2142    15/09/2014  S11.59.41.194   W077.07.33.184
Car     A       2123    15/09/2014  S12.15.12.245   W076.55.08.194
Truck   A       2123    16/09/2014  S13.42.48.122   W071.53.22.081
PickUp  C       2111    14/09/2014  S14.36.05.071   W075.11.47.133
PickUp  A       2111    15/09/2014  S14.39.51.245   W075.10.00.000
PickUp  A       2111    14/09/2014  S14.41.14.040   W075.07.12.245
Truck   B       2123    13/09/2014  S14.42.51.092   W075.05.35.133
Car     B       2142    14/09/2014  S14.46.14.040   W070.20.03.030
Truck   A       2123    13/09/2014  S15.54.53.163   W071.11.21.153
Truck   B       2123    16/09/2014  S15.58.40.051   W071.12.48.122
Car     A       2123    16/09/2014  S16.18.06.061   W069.16.24.122
Car     C       2142    13/09/2014  S16.29.27.092   W071.51.48.122

I want to select the record which has highest value of Date for each Model, ftype and serial, and also need to display the latitude and longitude, so my result should be like this:

Model   ftype   serial  Date        latitude        longitude
Car     A       2123    16/09/2014  S16.18.06.061   W069.16.24.122
Car     B       2142    15/09/2014  S11.59.41.194   W077.07.33.184
PickUp  A       2111    15/09/2014  S14.39.51.245   W075.10.00.000
PickUp  C       2111    14/09/2014  S14.36.05.071   W075.11.47.133
Truck   A       2123    16/09/2014  S13.42.48.122   W071.53.22.081
Truck   B       2123    16/09/2014  S15.58.40.051   W071.12.48.122

This needs an inner join with concatenate fields (Model, ftype and serial), I tried something as:

SELECT model + ftype + serial, date, latitude, longitude
FROM (
    SELECT model + ftype + serial, max(date) 
    FROM tgps group by model, ftype, serial) 
as xw inner join tgps on tgps.model + tgps.ftype + tgps.serial = xw.model + xw.ftype + xw.serial
and tgps.date = xw.max(date)

but it's not working.

the last part of your SQL doesn't make much sense to me. It should probably be `tgps.date = Max(xw.date)` I am not looking much more into this though so who knows if that will work. If you format the SQL to be more readable, it might get more answers. — gnomed, Sep 16 '14 at 18:48
also, "not working" is probably the worst possible description of a problem someone can give on stackoverflow. Provide logs and error messages in the future. — gnomed, Sep 16 '14 at 18:49
possible duplicate of [SQL: Find the max record per group](http://stackoverflow.com/questions/2657482/sql-find-the-max-record-per-group) — FuzzyTree, Sep 16 '14 at 18:54

score 1 · Answer 1 · answered Sep 16 '14 at 18:56

You can use a simple subquery linked to the main SELECT to get the max date from each entity:

select 
    Model, ftype, serial, Date, latitude, longitude
from
    tgps T
where
    Date = 
        (
            select
                max(Date)
            from
                tgps
            where
                Model = T.Model
                and ftype = T.ftype
                and serial = T.serial
        )

score 0 · Answer 2 · answered Sep 16 '14 at 18:50

You can use a CTE to get your max date for each model, ftype, and serial. Then you can join that to your table to get the lat and long for those values. SQL Fiddle

with CTE as
(select
 model,
 ftype,
 serial,
 max(date) as maxdate
from
tgps
group by
 model,
 ftype,
 serial
)

select
t1.*

from
tgps t1
inner join cte t2
  on t1.model = t2.model
  and t1.ftype = t2.ftype
  and t1.serial = t2.serial
  and t1.date = t2.maxdate

order by t1.model,
t1.ftype

score 0 · Answer 3 · answered Sep 16 '14 at 18:51

you can select all rows where another row with a greater date and the same model, ftype and serial does not exist

select * from tgps t1
where not exists (
    select 1 from tgps t2
    where t2.model = t1.model
    and t2.ftype = t1.ftype
    and t2.serial = t1.serial
    and t2.date > t1.date
)

or if you have access to analytic functions this will be faster

select * from (
  select *, 
  row_number() over (partition by model, ftype, serial order by date desc) rn
from tgps) t1 where rn = 1

score 0 · Answer 4 · edited Jan 28 '15 at 04:02

0

Thank you everybody, this is how I solved my problem:

SELECT tgps.model + tgps.ftype + tgps.serial,tgps.lat, tgps.lng, tgps.Date FROM tgps INNER JOIN (
    SELECT model, ftype, serial, max(Date) junto
    FROM tgps group by model, ftype, serial) 
as xw on tgps.Date = xw.junto and tgps.serial = xw.serial and tgps.ftype = xw.ftype and tgps.model = xw.model

edited Jan 28 '15 at 04:02

nempoBu4

6,521
8
35
40

answered Jan 28 '15 at 03:38

David Menacho

173
1
1
10

Find the max record per group, with additional fields

4 Answers4