Getting column value from two rows (in each return row) while grouping and stuck

[aweeks]

Hello,

 

I'm attempting to assemble a larger JOIN query using a SubSelect, but have realized my standalone SubSelect is not returning what I seek.

 

Query (this is to be my SubSelect):

 

 

SELECT s.primary_id, s.user_id, MAX(s.date) AS c_date, s.tradeshow_id  

FROM comments AS s 

GROUP BY s.tradeshow_id

 

This table houses comments for another table, and tracks a user_id and which tradeshow (is being commented on)

 

Example (primary_id is unique for each row, user_id & tradeshow_id are repeatable, wanting to sort by date, returning latest row PER tradeshow_id

 

primary_id        user_id         tradeshow_id           date

1                       1                   11                            2013-10-19 13:51:55

2                       1                   11                            2013-10-04 09:21:30

3                       5                   11                            2013-06-20 10:24:25

4                       1                   11                            2013-04-23 14:12:19

5                       5                   11                            2013-10-20 11:14:35

6                       1                   13                            2013-08-14 08:24:54

7                       5                   13                            2013-08-17 15:45:15

8                       5                   13                            2013-09-05 09:42:09

9                       5                   13                            2013-06-26 12:13:27

10                     1                   2                              2013-10-12 13:28:04

 

 

When looking at my results, for tradeshow 11 for example, the result the proper "MAX" date (2013-10-20), but in the same row, shows the primary_id of 1.  I, of course, want the row to stay linked.

 

Assistance would by greatly appreciated. I am stuck.

 

Thank you,

Adam

 

[Barand]

When you GROUP BY tradeshow_id you get a single row for each value of tradeshow_id. Columns that are selected but which are not aggregated nor in the group by clause could, according to the manual, come from any record in the group but usually from the first (which is why you get primary_id of 1). If you want the primary_id and user_id to come from the record that contains the MAX date then you need a JOIN on the id and date

SELECT s.primary_id, s.user_id, s.date, s.tradeshow_id
FROM comments as s
INNER JOIN 
    (
    SELECT tradeshow_id, MAX(date) as date
    FROM comments
    GROUP BY tradeshow_id
    ) as m
    USING (tradeshow_id, date)