I am trying to query a data base for among other things the last response to a posting. The table containing the responses has response id's, post id's, responses, and date of response. So I tried
I can get the last overall response with
SELECT response FROM responses WHERE response_ID in (SELECT top 1 response_id FROM responses ORDER BY date_response DESC);
But I need the last response for each posting. And I need it to be efficient because this is actually part of a larger statement.
Loading
Vijaya KadiyalaPosted Apr 10, 2008, 12:27 PM
Hi,
You can;t use TOP here, the only way that you can get this informationis by using Group By clause as mentioned by Manuel.
Thanks -- Vj
abhishek trivediPosted Feb 1, 2008, 6:23 AM
select top 1 response from tbl_response order by int_responseid desc
this is so simple and straight.
try it , if i am thinking in other way let me know pls...
ManuelPosted Aug 1, 2007, 4:29 PM
Example for syscolumns-Table:
SELECT sc.*
FROM
dbo.syscolumns scINNER
JOIN (SELECT id, MAX(colid) AS colid FROM dbo.syscolumns GROUP BY id) scmax ON sc.id = scmax.id AND sc.colid = scmax.colidReplace syscolumns with your table name and id and colid with your column names.