Title: POSTGRES - LIMIT 1 DESC for each ID in WHERE IN
Tags: sql;postgresql;greatest-n-per-group
Question: Problem:
I have a table where there are several rows with same code but different date, I also have a query:

```SELECT * FROM movies WHERE CODE IN ('action', 'comedy')
```

Question:
How can I return row with latest date FOR EACH code in the list and limit it to 1 since there can be duplicates:



so it returns just ID 1 and 4.

Something like 

```SELECT * FROM movies WHERE CODE IN ('action', 'comedy') DESC LIMIT 1
```

but for each code.

Thank you. 


Here is the accepted answer: In Postgres, use ```DISTINCT ON```:

```SELECT DISTINCT ON (CODE) m.*
FROM movies m
WHERE m.CODE IN ('action', 'comedy')
ORDER BY m.CODE, m.start_date DESC;
```

```DISTINCT ON``` is a Postgres extension.  It returns a result set with one row per unique combination of values of the expressions in parentheses (like ```GROUP BY``` keys in that respect).  The particular row is determined by the ```ORDER BY``` clause.  The unique keys come first and the rest of the keys define "which" row.


Here is another answer: ```SELECT code, MAX(start_date)
FROM movies
GROUP BY code
```

Comment for this answer: This won't return the id field (or fields other than code and start_date). The OP asks for SELECT *, so this won't work :(

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2019:00:05/29/19', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><postgresql><greatest-n-per-group>', 'Title': 'POSTGRES - LIMIT 1 DESC for each ID in WHERE IN', 'ViewCount': 1402, 'id': 56367940, 'user_AboutMe': '<p>A bit of this a bit of that.</p>\n', 'user_AccountId': 9169431.0, 'user_DisplayName': 'Anton Kim', 'user_Reputation': 797.0, 'user_Views': 118.0, 'user_id': 6817610.0}}