SQL filter top 1 row

Question

I ask you to help me in filtering a table where I have 3 columns (scanned tagID, date/time of scan, scaner mac address).

tagID	tme	mac
000A000B000C000D000E000F	04.07.2021T11:44:08Z	63584030653439
000A000B000C000D000E000F	04.07.2021T12.59.01Z	73594035653865
000A000B000C000D000E000F	04.07.2021T11:44:08Z	63584030653439
120A000B000C000D000E000F	04.07.2021T12.59.01Z	73594035653865
130A000B000C000D000E000F	04.07.2021T11:44:08Z	63584030653439
140A000B000C000D000E000F	04.07.2021T12.59.01Z	73594035653865
150A000B000C000D000E000F	04.07.2021T11:44:08Z	63584030653439
550A000B000C000D000E000F	04.07.2021T12.59.01Z	73594035653865
0067895B000C000D000E000F	04.07.2021T11:44:08Z	63584030653439
0007695B000C000D000E000F	04.07.2021T12.59.01Z	73594035653865
00459ASB000C000D000E000F	04.07.2021T11:44:08Z	63584030653439
0235466B000C000D000E000F	04.07.2021T12.59.01Z	73594035653865

From this table, I need to be filtered last 1 row for each mac, where tagID = "000A000B000C000D000E000F" and tme(date) = today.

Thanks in advance

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jul 04 '21 at 19:28

score 2 · Answer 1 · answered Jul 04 '21 at 11:26

2

In Standard SQL, this would look something like this:

select t.*
from (select t.*,
             row_number() over (partition by mac order by tme desc) as seqnum
      from t
      where tagID = '000A000B000C000D000E000F' and
            tme >= current_date and
            tme < current_date + interval '1 day'
     ) t
where seqnum = 1;

Date/time functions vary by database, so you might need to tweak those for your database.

Note: If you have only those three columns, then aggregation is sufficient:

select mac, tagID, max(tme)
from t
where tagID = '000A000B000C000D000E000F' and
      tme >= current_date and
      tme < current_date + interval '1 day'
group by mac, tagID

answered Jul 04 '21 at 11:26

Gordon Linoff

1,242,037
58
646
786

Thanks for your answer. I forgot to tell you, the tme column is declared as nvchar(50) and not date/time, and the table name is scans. With the command you told me, the SQL server return an error. – Vas Norbert Jul 04 '21 at 11:51
@VasNorbert . . . Fix your data model! In the mean time, convert the value to a proper date/time. – Gordon Linoff Jul 04 '21 at 13:58

SQL filter top 1 row

1 Answers1