Select ONLY second row if Date and Time are the same (MS SQL)

Question

A date, time, number is produced every 1 hour and this will be Try number 1. However, there are times when there is a second Try will happen to collect the number for the same date and Time.

I would like to select Date, Time and Number for Try number 2 instead of number 1 if it exist.

Output needed

Code

SELECT Date, Time, Number, Try
FROM DB1 DB1
WHERE (Date>={ts '2021-01-26 00:00:00'})
ORDER BY Date, Time

Please only tag the RDBMS you are *really* using (and then we can likely close this as the duplicate for the appropriate RDBMS). — Thom A, Aug 11 '21 at 09:58
Do **not**, **never**, **never ever** post images of tables. Post the `CREATE` and `INSERT` statements as **text**. — sticky bit, Aug 11 '21 at 10:19
@stickybit Post the CREATE and INSERT statement of the table? Wouldn't a image of the problem and solution that is intended clearer? — fluervion, Aug 11 '21 at 10:29
An image isn't consumable. If somebody wants to work on your problem and setup a test environment for doing so, they'd be forced to type everything of the image by hand. Also vision impaired people relying on screen readers are basically excluded from the question not only being unable to answer it but also not to learn from it (Remember questions are supposed to be of value also to future readers, not only to help the one who asked.). So don't use images for everything that can be expressed as text. And a table can, by the `CREATE` and `INSERT` statements. — sticky bit, Aug 11 '21 at 10:38
Could there be a 3, or a 4, etc? Or will it always be 1 or 2? — Thom A, Aug 11 '21 at 10:50
@Charlieface It doesnt. I would like to choose Try number 2 over number 1 if it exists for the particular Date and Time. — fluervion, Aug 12 '21 at 14:11
@fluervion 1 is after 2, if you put your data in the other direction... — Thom A, Aug 12 '21 at 14:16

score 0 · Answer 1 · answered Aug 11 '21 at 10:07

0

Use row_number and filter:

select Date, Time, Number, Try
from
(
SELECT Date, Time, Number, Try, 
       row_number() over(partition by Date, Time order by Try desc) as rn
) s
where rn=1

It will selecft only the last try per date, time

answered Aug 11 '21 at 10:07

leftjoin

36,950
8
57
116

score 0 · Answer 2 · answered Aug 11 '21 at 10:31

You can use not exists, if you specifically want "2" (as the question states):

select t.*
from db1 t
where t.try = 2 or
      (t.try = 1 and
       not exists (select 1
                   from db1 t2
                   where t2.date = t.date and t2.time = t.time and
                         t2.try = 1
                  )
      );

score -1 · Answer 3 · answered Aug 11 '21 at 10:19

SELECT * FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RoNum
              , ID --Add any fields needed here (or replace ID by *)
        FROM TABLE_NAME
) AS tbl 
WHERE (Date>={ts '2021-01-26 00:00:00'})
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY;

The OFFSET clause skips zero rows and the FETCH clause returns the first row.

Reference: https://www.sqltutorial.org/sql-fetch/

Select ONLY second row if Date and Time are the same (MS SQL)

3 Answers3