2

I have a table like:

|serialnumber|     location     |    medium |address|    date      | value
| 320921     | Pomieszczenie C  | Gaz       |  9    | 2019-12-14   | 547
| 320921     | Pomieszczenie C  | Gaz       |  9    | 2019-12-16   | 626
| 374836     | Pomieszczenie B  | Wodomierz |  5    | 2019-12-28   | 1728
| 374836     | Pomieszczenie B  | Wodomierz |  5    | 2020-01-01   | 1998

I want to get the rows with max date, based on a group by serialNumber, result like this:

|serialnumber|     location     |    medium |address|    date      | value
| 320921     | Pomieszczenie C  | Gaz       |  9    | 2019-12-16   | 626
| 374836     | Pomieszczenie B  | Wodomierz |  5    | 2020-01-01   | 1998

Could you please share your ideas?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
MarWinProg
  • 23
  • 3

2 Answers2

1

From your sample data it seems that you want the row with the latest date for each combination of serialnumber and location.
You can do it with NOT EXISTS:

select t.* from tablename t
where not exists (
  select 1 from tablename
  where serialnumber = t.serialnumber and location = t.location and date > t.date
)

See the demo.
Results:

| serialnumber | location        | medium    | address | date                | value |
| ------------ | --------------- | --------- | ------- | ------------------- | ----- |
| 320921       | Pomieszczenie C | Gaz       | 9       | 2019-12-16 00:00:00 | 626   |
| 374836       | Pomieszczenie B | Wodomierz | 5       | 2020-01-01 00:00:00 | 1998  |
forpas
  • 160,666
  • 10
  • 38
  • 76
1

I would use a correlated subquery for filtering (along with an index on (serialnumber, date) for performance):

select t.*
from mytable t
where t.date = (
    select max(t1.date)
    from mytable t1
    where t1.serial_number = t.serial_number
)

Depending on the size and distribution of your dataset, you might want to consider a join with an aggregate query:

select t.*
from mytable t
inner join (
    select serial_number, max(date) max_date
    from mytable
    group by serial_number
) t1
    on  t1.serial_number = t.serial_number
    and t1.max_date = t.date
GMB
  • 216,147
  • 25
  • 84
  • 135
  • The first answer given here is slow due to doing multiple full table scans. The second is much more efficient. It needs `INDEX(serial_number, date)`. – Rick James Feb 05 '20 at 04:33