Running a query with PHP/MySQL then reordering the results by another column

Question

This SQL query gives me the results I want; however, I want the results ordered by a different column:

SELECT * 
FROM post 
    INNER JOIN account ON post.account_id = account.account_id 
WHERE post_id > new 
ORDER BY post_date 
ASC LIMIT 10;

I can not simply change ORDER BY post_date ASC to ORDER BY post_id DESC, while that will in fact order the query the way I want it... it will give me the wrong 10 posts.

I simply want to take the EXACT RESULTS of the above query then reorder the results by the post_id.
I would like to do this with SQL if possible, if not I could order the results by adding the results into a new array reversed.

Your query should fail, returning MySQL error 1060: Duplicate column name because of the account_id existing in both tables. — OMG Ponies
– OMG Ponies, Commented Mar 1, 2010 at 22:21
It did, I made a few changes to the database and it all works like a charm now, thanks for pointing that out! — Cody.Stewart
– Cody.Stewart, Commented Mar 1, 2010 at 22:38

cletus · Accepted Answer · 2010-03-01 22:13:58Z

6

Use a subquery to reorder:

SELECT * FROM (
  SELECT *
  FROM post
  INNER JOIN account ON post.account_id = account.account_id
  WHERE post_id > neww
  ORDER BY post_date ASC LIMIT 10;
) ORDER BY post_id

answered Mar 1, 2010 at 22:13

cletus

627k169 gold badges922 silver badges945 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cody.Stewart Over a year ago

Thanks worked like a charm, I cant believe I have been searching 2 days for this :(...

sholsinger Over a year ago

Thanks, I thought that this would be the way to go with my own problem (On SQL Server 2k8). You've confirmed it for me. +1

Mike Over a year ago

This query is missing AS, should be ... ) AS x ORDER BY post_id. See dev.mysql.com/doc/refman/5.7/en/from-clause-subqueries.html

Mark Byers · Accepted Answer · 2010-03-01 22:13:06Z

4

Use a subquery:

SELECT * FROM (
    SELECT *
    FROM post
    INNER JOIN account
    ON post.account_id = account.account_id
    WHERE post_id > neww
    ORDER BY post_date ASC
    LIMIT 10) AS T1
ORDER BY post_id DESC

answered Mar 1, 2010 at 22:13

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

1 Comment

sholsinger Over a year ago

Wow, Notice the race condition that occurred here. (both posted Mar 1 at 22:13) While both answers are correct only one can get the "correct" answer. I find it interesting that Mark's answer names the sub-query T1 and then doesn't reference it.

Collectives™ on Stack Overflow

Running a query with PHP/MySQL then reordering the results by another column

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related