CODEWITHSUNDEEP
sql
William
William

Reputation: 8818

Whats wrong with my SQL query?

I'm trying to set up a query that shows the first post of each thread and is ordered by the date of the last post in each thread. I got the first part down with this query:

SELECT *
FROM (
SELECT Min( ID ) AS MinID
FROM test_posts
GROUP BY Thread
)tmin

JOIN test_posts ON test_posts.ID = tmin.MinID

Now I need to figure out how to call the last post of each thread into a table, than use that table to order the first tables results. So far I got this, but it doesn't work.

SELECT *
FROM (
SELECT Min( ID ) AS MinID
FROM test_posts
GROUP BY Thread
)tmin

JOIN test_posts ON test_posts.ID = tmin.MinID 

ORDER BY (SELECT MAX( ID ) AS MaxID, Thread, MAX( Date )
FROM test_posts
GROUP BY Thread
)tmax

tmax.Date

Upvotes: 1

Views: 124

Answers (1)

Adam Musch
Adam Musch

Reputation: 13628

select minid
  from (select min(id) as min_id, 
               max(id) as max_id, 
               max(date) as max_date
               thread
          from test_posts
         group by thread ) t_min_max
       inner join test_posts on test_posts.id = t_min_max.min_id
 order by max_id, thread, max_date

You can't order by a subquery, but you can order by columns/expressions from a preceding subquery. I'm not exactly sure how you intend to sort it, but you've got all the expressions there.

Upvotes: 5

Related Questions