How to find required unique result

Question

I have two table 'topic' and 'subcategory'

I am using this query--

Select * from `subcategory` as s 
Inner join `topic` as f
WHERE s.`Subcategory_id` = f.`Subcategory_id

My result shows like

Category_id Subcategory_id Post_id time

    2        2.3         4    2012-12-01
    1        1.5         5    2013-01-20
    1        1.3         6    2013-03-18

There's also other columns... but all I want is to select the latest Post_id and Subcategory_id of one Category_id ... that means here Category 1 has two Subcategory it will select only the latest(here 1.3) and same result all the time for all Category when database will grown larger. What will be the next query or how could I change the existing query to gain my desired result?

Answer 1

SELECT Post_Id, Subcategory_Id from subcategory as s, topic as t where 
   s.Subcategory_id = t.Subcategory_id and time = (
      SELECT Max(time) from subcategory as s1, topic as t1 where 
         s1.Subcategory_id = t1.Subcategory_id and s1.Category_id = s.Category_id
   );

Something like that, I think, will work.

Answer 2

SELECT TOP 1 ... ORDER BY whatever column determines "the latest"

e.g.

SELECT TOP 1 ... ORDER BY TIME DESCENDING

Or in case of mysql:

SELECT ... ORDER BY TIME DESCENDING LIMIT 1