Approach to Selecting top item matching a criteria
- by jkelley
I have a SQL problem that I've come up against routinely, and normally just solved w/ a nested query. I'm hoping someone can suggest a more elegant solution.
It often happens that I need to select a result set for a user, conditioned upon it being the most recent, or the most sizeable or whatever.
For example: Their complete list of pages created, but I only want the most recent name they applied to a page. It so happens that the database contains many entries for each page, and only the most recent one is desired.
I've been using a nested select like:
SELECT pg.customName, pg.id
FROM (
select id, max(createdAt) as mostRecent
from pages
where userId = @UserId
GROUP BY id
) as MostRecentPages
JOIN pages pg
ON pg.id = MostRecentPages.id
AND pg.createdAt = MostRecentPages.mostRecent
Is there a better syntax to perform this selection?
