Your query raises a few questions about your data model and that makes it hard to give a confident answer.
- Why are you doing a
LEFT JOINon some of your tables when you specifically want your results to include rows where matches in those table exist?
- Why are you joining in
POSTwhen it is not used anywhere?
- Why does
ROLE_IDcolumn, if one user can take on multiple roles?
So, ignoring all that, I’d suggest you look in to the
CROSS APPLY feature in conjuction with
FETCH FIRST x ROWS ONLY. You can get a list of users from
CROSS APPLY those with the first record for
ROLE_POST that has the state you are looking for. Because you’re only joining to 1 row for each user, you’ll still just have one row per user at the end of that. Then, limit your results to whichever 10 users you want from that list (e.g., the 10 most recently created users).
Put all together, it might look something like this:
SELECT u.username, u.full_name, u.created_at, x.role_id r_id, x.state FROM user_account u CROSS APPLY ( SELECT r.role_id, rp.state FROM role r INNER JOIN role_post rp ON rp.role_id = r.role_id WHERE r.user_id = u.user_id AND rp.state="001" -- Best to ORDER BY something here so results are predictable and consistent ORDER BY ??? FETCH FIRST 1 ROW ONLY ) x ORDER BY u.created_at DESC FETCH FIRST 10 ROWS ONLY;