Selecting last response for multiple postings.

Question

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.

Vijaya Kadiyala · Answer

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 trivedi · Answer

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...

Manuel · Answer

Example for syscolumns-Table: SELECT sc .* FROM dbo . syscolumns sc INNER JOIN ( SELECT id  MAX ( colid ) AS colid FROM dbo . syscolumns GROUP BY id ) scmax ON sc . id = scmax . id AND sc . colid = scmax . colid Replace syscolumns with your table name and id and colid with your column names.