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?

Andrey Korneyev · Accepted Answer · 2016-11-17 09:56:47Z

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

answered Nov 17, 2016 at 9:56

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mudassir Hasan · Accepted Answer · 2016-11-17 12:21:20Z

1

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

edited Nov 17, 2016 at 12:21

answered Nov 17, 2016 at 9:58

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

1 Comment

DatumPoint Over a year ago

Just as a point of pedantic clarification :-), it is a misconception that CTEs need to be preceded by a semicolon. The semicolon actually terminates the previous statement (of which there is none in this example). Also note that row_number() is not a ranking function. rank() and dense_rank() are. Your data will be less prone to misinterpretation if you don't call that column rnk. Something like seq or sequence_number might be a better option.

Collectives™ on Stack Overflow

Display results from sql while loop in a single table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related