-2

Take this users and status list:

enter image description here

I need an SQL that will give me a list of users where the latest status is True - that will be users: 2 and 5 (user 4 latest status is False)

How can archive this?

MojoDK
  • 4,410
  • 10
  • 42
  • 80
  • 3
    Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Thom A Dec 15 '21 at 13:22
  • 2
    Most people here want sample table data _and the expected result_ as formatted text, not as images. Also show us your current query attempt. I.e. [mcve]. – jarlh Dec 15 '21 at 13:30
  • Personally, I like the cross apply approach. but as with everything; it depends on your environment: https://stackoverflow.com/questions/42451972/cross-apply-select-top-1-much-slower-than-row-number – xQbert Dec 15 '21 at 13:34
  • Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. How do you determine the "latest" `Status` when all of the columns are datatype `pixel`? What results do you expect from the sample data? What have you tried? Your actual question is quite perplexing: "How can archive this?" – HABO Dec 15 '21 at 14:30

1 Answers1

0

Use ROW_NUMBER and then filter on status is true:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY StatusDate DESC) rn
    FROM yourTable
)

SELECT UserID
FROM cte
WHERE rn = 1 AND Status = 'True';
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360