Home / Questions / 433734
Accepted
Nicolas Oñate
Asked: 2022-03-09 02:49:47 +0800 CST 2022-03-09 02:49:47 +0800 CST

Query to group and filter by latest dates

  • 772

I have a table that has the following format (int, int, DateTime)

Id  |  IdOperacion  |  Fecha
 1  |            1  |  08/03/2021 10:00:00.00
 2  |            2  |  08/03/2021 10:10:00.00
 3  |            3  |  08/03/2021 10:20:00.00
 4  |            1  |  08/03/2021 11:00:00.00
 5  |            2  |  08/03/2021 11:10:00.00
 6  |            3  |  08/03/2021 11:20:00.00

I have tried to obtain the last dates so that the table looks like this:

Id  |  IdOperacion  |  Fecha
 4  |            1  |  08/03/2021 11:00:00.00
 5  |            2  |  08/03/2021 11:10:00.00
 6  |            3  |  08/03/2021 11:20:00.00

Removing the operations with last date but being variable I don't know how to do it:

SELECT Id, IdOperacion, Fecha FROM Tabla WHERE Fecha >= Convert(datetime, GETDATE())
sql

2 Answers

  1. Best Answer

    There are probably several options to handle your question. I propose one based on formatting the date so that it only contains the date and time (without minutes and seconds) and use this to filter the data.

    The first part is to get the date with just the time. For that we use convert:

    convert(char(10), horainicio, 112) + ' ' + convert(char(2), horainicio, 108)

    the first part returns the date as yyyymmdd, and the second part returns the time in hh:mm:ss. But since in the conversion we use char(2), we get only the first two characters, that is, the time. The result it returns is something like this:

    20210308   10

    Once that problem is solved, we have to get what is the largest date/time in our table. This is simple: we simply order by date in descending order and we are left with the first result:

    select top 1 convert(char(10), fecha, 112)
+ ' ' + convert(char(2), fecha, 108) as fechamax
from Tabla
order by fechamax desc

    The last step would be to obtain the rows of the table whose converted date matches the data of this subquery:

    select * from Tabla
where convert(char(10), fecha, 112)+ ' ' + convert(char(2), fecha, 108) =  
( select top 1 convert(char(10), fecha, 112)
+ ' ' + convert(char(2), fecha, 108) as fechamax
from Tabla
order by fechamax desc)
    • 3

  2. We will split the problem in two. First we get the last date for each element using the aggregation function MAX(), then we will join the result (one row per idOperacion) with the table query, getting only the last value.

    SELECT a.Id, a.IdOperacion, b.FechaMax
FROM Tabla a
INNER JOIN (
   SELECT IdOperacion , MAX(Fecha) as FechaMax
   FROM Tabla
   GROUP BY IdOperacion 
) b on a.IdOperacion = b.IdOperacion
    • 3