jenny jenny - 22 days ago 8
SQL Question

Display results from sql while loop in a single table

In my SQL-server database there is a table in which each day has many entries for each time value (in integer form e.g. 800 --> 08:00) with different amounts and a unique ID field.

My table looks something like this:

ID NTIME AMOUNT
8426628 828 531.81
8426629 828 782.61
8426630 829 183.41
8426631 829 183.41
8426632 829 832.41
8426633 829 32.41
8426634 830 374.41
8426635 830 78.41
8426636 830 628.41


As a result I want to get the rows with the maximum id for each distinct time value, like this:

ID NTIME AMOUNT
8426629 828 782.61
8426633 829 32.41
8426636 830 628.41


I have tried the following query:

DECLARE @t int=815;
WHILE @t<=830
BEGIN
select ID, NTIME, AMOUNT FROM <my_table> WHERE
NTIME=@t and
ID =
(select max(ID) FROM <my_table> where NTIME=@t);
Set @t = @t + 1;
END


Which fetches the correct results, but each select query result is displayed in a different table (result 1-1, result 1-2 etc.). Is there a way to get the results in a single result table, preferably without creating a temporary table?

Answer

You can use group by and query like this:

select
    T1.ID,
    T1.NTIME,
    T1.AMOUNT
from
(
   select NTIME, max(ID) as ID
   from <my_table>
   group by NTIME
) as T
left outer join <my_table> as T1 on T1.ID = T.ID and T1.NTIME = T.NTIME

Inner query outputs max ID for each distinct NTIME and then join back to table to get AMOUNT for these pairs of ID and NTIME