CODEWITHSUNDEEP
sqlpostgresql
Dasun
Dasun

Reputation: 83

How do I select 1 column from a SQL query which is ordered by date?

So I have a private messages model which has the columns: sender_id, recipient_id, content, and created_at.

I want to select the last 5 recipients I have messaged. However, I can't figure out how to narrow down the columns.

My current query is this:

SELECT DISTINCT recipient_id, created_at FROM private_messages WHERE sender_id = :user_id ORDER BY created_at DESC LIMIT 5

I can't get rid of the created_at column since it's necessary for the ORDER BY. I must be missing something, but I'm not entirely sure what it is.

Upvotes: 0

Views: 157

Answers (6)

Oto Shavadze
Oto Shavadze

Reputation: 42753

Try this 

 WITH t AS ( SELECT max(created_at) AS created_at, recipient_id  FROM messages 
 GROUP BY recipient_id  )

 SELECT created_at, recipient_id   FROM t ORDER BY created_at DESC LIMIT 5

Upvotes: 0

Beryllium
Beryllium

Reputation: 12988

You can't omit the column. This has been added to the SQL standard some time ago.

for SELECT DISTINCT, ORDER BY expressions must appear in select list

Quoted from here (message from Tom Lane.)

there's actually a definitional reason for it. Consider

SELECT DISTINCT x FROM tab ORDER BY y;

For any particular x-value in the table there might be many different y
values. Which one will you use to sort that x-value in the output?

Back in SQL92 they avoided this problem by specifying that ORDER BY
entries had to reference output columns. SQL99 has some messy verbiage that I think comes out at the same place as our restriction

For example, Oracle does not enforce this rule (10g or 11g).

Upvotes: 0

chenyijiu
chenyijiu

Reputation: 1

try: SELECT recipient_id FROM(SELECT recipient_id,MAX(created_at) as created_at FROM private_messages WHERE sender_id = :user_id GROUP BY recipient_id)a ORDER BY created_at DESC LIMIT 5;

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

....I can't get rid of the created_at column since it's necessary for the ORDER BY.

No.

A column in Order By cluase is not necessary to be present in SELECT column list. You can omit created_at column in SELECT list of your query . Rest of your query will work fine.

Check this SQL FIDDLE where above fact is shown.

Upvotes: 2

passion
passion

Reputation: 44

try this: SELECT DISTINCT recipient_id, created_at FROM private_messages WHERE sender_id = :user_id GROUP BY recipient_id ORDER BY created_at DESC LIMIT 5

Upvotes: 0

JanR
JanR

Reputation: 6132

Have you tried something like the below:

SELECT TOP 5  recipient_id
FROM private_messages 
WHERE sender_id = :user_id 
GROUP BY recipient_id
ORDER BY created_at DESC

I believe you don't need created_at in your select to use it in an order by clause. This might depend on which database system you are using tho, in SQL server it will allow this.

Upvotes: 0

Related Questions