CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-by
Fabi
Fabi

Reputation: 153

Two (in my opinion) equivalent SQL queries return different results

I have this query which returns the (correct) result in the screenshot below:

SELECT owner_id, MAX(ctime) as post_time, aid
FROM `jul_cpg15x_pictures`
GROUP BY aid
ORDER BY aid DESC

Result of Query 1

But I need the query in the following format (because I want to make some joins afterwards):

SELECT * FROM 
(SELECT owner_id, MAX(ctime) as post_time, aid
FROM `jul_cpg15x_pictures`
GROUP BY aid
ORDER BY aid DESC) q

This query returns a different result (some owner_ids are different): Result of Query 2

This is driving me crazy for hours now, how can that be? I double checked that the ctime is distinct in the table and does not occur twice. But for example for the ctime 1410003221 there is definitely only one entry (the one with owner_id = 8).

Thank you so much for any help or hints.

Upvotes: 1

Views: 84

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272106

This query:

SELECT owner_id, MAX(ctime) as post_time, aid
FROM `jul_cpg15x_pictures`
GROUP BY aid
ORDER BY aid DESC

Uses a partial group by. You are selecting unaggregated values of owner_id and aid columns, but you are grouping only by aid. MySQL is free to return any owner_id for a given aid group. the ORDER BY clause does not help since ordering is performed after grouping.

So, if your data looks like:

owner_id  ctime       aid
--------  ----------  ---
8         1410003221  176
2         1410000000  176

Then both results are correct (both rows collapsed into one because of same aid, max ctime calculated as expected but owner_id from either row could be returned):

owner_id  post_time   aid
--------  ----------  ---
8         1410003221  176

owner_id  post_time   aid
--------  ----------  ---
2         1410003221  176

Having said all that, if your intention is to grab the owner_id of latest post for a given aid you could write your query like this:

SELECT s1.aid, s1.ctime, s1.owner_id
FROM jul_cpg15x_pictures s1
JOIN (
    SELECT aid, MAX(ctime) AS ctime
    FROM jul_cpg15x_pictures
    GROUP BY aid
) AS s2 ON s1.aid = s2.aid AND s1.ctime = s2.ctime

Upvotes: 5

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try the following, you can add more columns from the table if needed:

select t.owner_id, t.aid, t.ctime
  from jul_cpg15x_pictures t
  join (select aid, max(ctime) as last_post_time,
          from jul_cpg15x_pictures
         group by aid) v
    on t.aid = v.aid
   and t.ctime = v.last_post_time
 order by t.aid desc

As you indicated was your intention, per your comments, this query will show the last post_time for each aid, and then the corresponding owner_id.

Upvotes: 1

Related Questions