0

i have a MSSQL-Table and i want to query the latest dataset for each user.

This is the table.

id = BIGINT AI NN

user = varchar(50) NN

status = TEXT

insertDate = GetDate()

id user status insertDate
1 test ok 2023-01-13 08:11:03.290
2 ross Not OK 2023-01-13 08:11:27.667
3 rachel Not OK 2023-01-13 08:13:37.850
4 rachel OK 2023-01-13 08:21:48.727
5 test OK 2023-01-13 08:53:31.440
6 ross OK 2023-01-13 09:12:50.930

And the result should be

id user status insertDate
4 rachel OK 2023-01-13 08:21:48.727
5 test OK 2023-01-13 08:53:31.440
6 ross OK 2023-01-13 09:12:50.930

I have tried this code

 select *, 
  
  (select MAX(id) maxId from table b WHERE a.user = b.user Group By b.user)
  from table a

but i get all Entries instead only the last entries.

thanks in advance

Tobi
  • 53
  • 1
  • 9
  • *"status = TEXT"* I *hope* you don't mean the `text` data type; `text` has been deprecated since **2005**. Are you *really* likely to have a status that is over 8,000 characters in length as well? That seems unlikely. – Thom A Jan 13 '23 at 10:07
  • You need to use JOIN or at least IN within the outer query (or of course, this could also do using window functions), otherwise your subquery has no effect. There are lots of duplicates for this question. Did you google this before creating your question? – Jonas Metzler Jan 13 '23 at 10:09

0 Answers0