SQL Server - Bring extra data with a Group By

Question

SQL Server - Bring extra data with a Group By

Navigation

#1 by (1 votes)

1

I have a table with different terminals. In it, the terminal number and its different measurements are recorded every 15 minutes (that is, the terminal number will be repeated many times in the table, but each record with a different date). Example:

Now, what I need to bring the data of the last measurement for each terminal. I tried something like this:

select terminal, max(fechahora) as 'Ultima medicion' from lecturas group by terminal order by max(fechahora) desc

And obviously it brings me this:

But how do I do now to bring the other values of each of those measurements? That is, I need a table like the first, but only with the last measurement for each terminal (There would be only one record per terminal).

Thank you!

sql sql-server

asked by ZottoSL 19.09.2018 в 15:55

source

1 answer

Modal window can not be closed because it has opacity on top You doubt how to get a user's location

score 1 · Accepted Answer

To bring the data that you are missing, you only have to re-join that query with the original table.

Since what you want is exactly that row, your condition of join are the two columns that you already brought, to make sure that the data is from those rows.

Then the query would be something like this:

Select b.* from (
select terminal, max(fechahora) as 'medicion' from lecturas group by terminal ) a left join lecturas b on a.terminal = b.terminal and a.medicion = b.fechahora 
Order by b.fechahora desc