Display results from sql while loop in a single table

Question

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 1

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

Answer 2

This will give you the desired result

;WITH cte AS
(
SELECT id,ntime, amount
row_number() over (partition by ntime order by id desc) seq
)

select id,ntime, amount
from cte
where seq=1