CODEWITHSUNDEEP
sql
Shlomo
Shlomo

Reputation: 3990

Group by A but order by B

I am trying to group on column A but also order the groups by column B. I cannot get it to work. Also I want to join on another table.

select t.*, db_users.user as username, db_users.thumbnail 
from 
( 
  SELECT db_chats.* FROM db_chats WHERE db_chats.user = 'utQ8YDxD6kSrlI5QtFOUAE4h' 
  group by db_chats.partner 
  order by db_chats.timestamp desc 
) 
as t 
inner join db_users on t.partner = db_users.id

This query gives me only 1 row and it is not sorted by timestamp of the grouped column. How can I get this to work?

Here is a sqlfiddle

Expected result:

2 rows 
user                         partner                     timestamp
------------------------     ------------------------    -------------------
utQ8YDxD6kSrlI5QtFOUAE4h     6h4rpXOXMB456CJOz8elP2zM    
utQ8YDxD6kSrlI5QtFOUAE4h     D5RgVSRdbp9kPp9ua9q9rtdf    1413572130  sorted!!!

Upvotes: 0

Views: 75

Answers (3)

xQbert
xQbert

Reputation: 35333

Assuming you after the last chat message from each partner for a given user...

We need to get a set of data that consists of the max time-stamp for each user partner (thus the inner select)

We then join this back to the chat table to get a all the additional desired attributes.

The reason why you're initial queries were not working were because you were not limiting results to an aggregate (Max), which is why I had so much trouble understanding why you needed a group by.

Once you clarified in plain text English what you were after and provided examples, we could figure it out.

SELECT A.*, DU.user as username, du.thumbnail
FROM DB_CHATS A
INNER JOIN (
   SELECT max(timestamp) TS, user, partner
   FROM db_chats
   GROUP BY  user,partner) T
  on A.TimeStamp=T.TS 
 and A.user=T.User
 and A.Partner = T.Partner
LEFT JOIN  db_users DU on t.partner = DU.id 
WHERE A.user = 'utQ8YDxD6kSrlI5QtFOUAE4h'

I think this is what you're after (updated) http://sqlfiddle.com/#!2/8abcac/16/0

I used a left join on the chance that a partner has account deleted in db_users but records still exist in DB_Chats.

You may want to use an INNER JOIN to exclude DB_USERS that are no longer in the system however... you're call don't know the need.

Upvotes: 1

Deep
Deep

Reputation: 3202

You may want this:

select t.*, db_users.user as username, db_users.thumbnail 
from 
( 
  SELECT db_chats.* FROM db_chats 
  group by db_chats.partner 
) 
as t 
inner join db_users on t.partner = db_users.id 
order by t.timestamp desc

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

SELECT t1.* FROM db_chats t1 
WHERE t1.user = 'utQ8YDxD6kSrlI5QtFOUAE4h'
AND NOT EXISTS (SELECT 1 FROM db_chats t2
                WHERE t2.user = t1.user
                AND t2.partner = t1.partner
                AND t2.timestamp > t1.timestamp);

http://sqlfiddle.com/#!2/4f44b/27

Upvotes: 1

Related Questions