sql query prob.

3 posts · originally from https://geek.digit.in/community/threads/sql-query-prob.35789/

i m having two tabels in ms sql server ..one is catagory_master(cat_id,cat_nm) and another is user_post(cat_id,post_date)..and both have cat_id in common..so i want to display all cat_nm in left side but in right hand side i only want to display only one row which has highest date(last post) in it(i.e in post_date)..so can u tell me the qury for that..i have used left outer join also...hint:- i m devloping forum just like thinkdigit and if u have seen in thinkdigit forum..there is only one last post displayed with its catagory(bydemand,software etc.) i want same thing..

select
b.cat_nm,
a.post_date
from
user_post a join catagory_master b
on (b.cat_id=a.cat_id)
Where
a.post_date=(select max(c.post_date) from user_post c where c.cat_id=a.cat_id)

This correlated subquery should do the trick.

Note: However this query would not bring back a category without a post. I feel that the elegant way would be to get all categories & the posts for a category using two different queries and combine them in the front end

thaxs man 4 ur sugg. i will have to find some another way