Using a DISTINCT clause to filter data but still pull other fields that are not DISTINCT

Question

I am trying to write a query in Postgresql that pulls a set of ordered data and filters it by a distinct field. I also need to pull several other fields from the same table row, but they need to be left out of the distinct evaluation. example:

  SELECT DISTINCT(user_id) user_id, 
         created_at 
    FROM creations 
ORDER BY created_at   
   LIMIT 20

I need the user_id to be DISTINCT, but don't care whether the created_at date is unique or not. Because the created_at date is being included in the evaluation, I am getting duplicate user_id's in my result set.

Also, the data must be ordered by the date, so using DISTINCT ON is not an option here. It required that the DISTINCT ON field be the first field in the ORDER clause and that does not deliver the results that I seek.

How do I properly use the DISTINCT clause but limit its scope to only one field while still selecting other fields?

Answer 1

Using a sub-query was suggested by someone on the irc #postgresql channel. It worked:

SELECT user_id  
FROM (SELECT DISTINCT ON (user_id) * FROM creations) ss  
ORDER BY created_at DESC  
LIMIT 20;

Answer 2

As you've discovered, standard SQL treats DISTINCT as applying to the whole select-list, not just one column or a few columns. The reason for this is that it's ambiguous what value to put in the columns you exclude from the DISTINCT. For the same reason, standard SQL doesn't allow you to have ambiguous columns in a query with GROUP BY.

But PostgreSQL has a nonstandard extension to SQL to allow for what you're asking: DISTINCT ON (expr).

SELECT DISTINCT ON (user_id) user_id, created_at 
FROM creations 
ORDER BY user_id, created_at   
LIMIT 20

You have to include the distinct expression(s) as the leftmost part of your ORDER BY clause.

See the manual on DISTINCT Clause for more information.

Answer 3

The GROUP BY should ensure distinct values of the grouped columns, this might give you what you are after.

(Note I'm putting in my 2 cents even though I am not familiar with PostgreSQL, but rather MySQL and Oracle)

In MySql

SELECT user_id, created_at
FROM creations
GROUP BY user_id
ORDER BY user_id

In Oracle sqlplus

SELECT user_id, FIRST(created_at)
FROM creations
GROUP BY user_id
ORDER BY user_id

These will give you the user_id followed by the first created_at associated with that user_id. If you want a different created_at you have the option to substitute FIRST with other functions like AVG, MIN, MAX, or LAST in Oracle, you can also try adding ORDER BY on other columns (including ones that are not returned, to give you a different created_at.

Answer 4

Your question is not well defined - when you say you need also other data from the same row you are not defining which row.

You do say you need to order the results by created_at, so I will assume that you want values from the row with min created_at (earliest).

This now becomes one of the most common so SQL questions - retrieving rows containing some aggregate value (MIN, MAX).

For example

SELECT user_id, MIN(created_at) AS created_at
FROM creations
GROUP BY user_id
ORDER BY MIN(create_at)
LIMIT 20

This approach will not let you (easily) pick other values from the same row.

One approach that will let you pick other values is

SELECT c.user_id, c.created_at, c.other_columns
FROM creations c LEFT JOIN creation c_help
     ON c.user_id = c_help.user_id AND c.created_at > c_help.create_at
WHERE c_help IS NULL
ORDER BY c.created_at
LIMIT 20

Answer 5

If you want the most recent created_at for each user then I suggest you aggregate like this:

SELECT user_id, MAX(created_at)
FROM creations
WHERE ....
GROUP BY user_id
ORDER BY created_at DESC

This will return the most recent created_at for each user_id If you only want the top 20, then append

LIMIT 20

EDIT: This is basically the same thing Unreason said above... define from which row you want the data by aggregation.