Mr. stefan, Thanks for let me know about basic of sql. But if you see my earlier response, i have given the correct sql
select * from tab where anwer_timestamp in (select max(anwer_timestamp) from tab where q_id in (select distinct q_id from tab) group by q_id); You missed reading my earlier email, where my basic on sql is good enough. I wrongly typed the sql and missed one "where" condition in my later response. Also, please understand, people on this forum are trying to help each other. Some might give the required solution with exact requirement, while some give just hints, which helps the requester in solving the issue. Please be appreciative of the people trying to provide some kind of solution. hope this does not sound too harsh on u On Thu, Mar 14, 2013 at 3:44 PM, Stefan Kuhn <stef...@web.de> wrote: > > not all the rows, only the distinct q_id, > The subquery will give all distinct q_ids, but the select from > in(subquery) will give everything, because each row must have a q_id which > is returned by the subquery. The query after all says "select all rows > where the q_id is one of the q_ids occuring in the table" and this, of > course, gives everything. > Also your select qid, max(atimestamp) is not doing what I think you want > to do. I think you want all raws where atimestamp is somehow a maximum. Now > in your query max(atimesamp) will return one single value and this will be > used as a sort of constant. So if max(atimestamp) is e. g. 5, you get > effictively select qid, 5 from ... Qou will get all rows with the constant > becoming a row. You want to filter rows, so your condition must be in where > (this is a basic concept of sql). Somehow (this is not correct sql, just a > hint) it must be like select qid from kkk where atimestamp=max(atimestamp). > If you want to filter the condition must be in where, a function on a > column behins select will not filter. > I think you need to get a basic understanding of sql first, sorry if that > sounds harsh > Stefan > > > On Wed, Mar 13, 2013 at 8:28 PM, Johan De Meersman <vegiv...@tuxera.be > >wrote: > > > ------------------------------ > > > > *From: *"Ananda Kumar" <anan...@gmail.com> > > *Subject: *Re: Retrieve most recent of multiple rows > > > > > > > > select qid,max(atimestamp) from kkk where qid in (select distinct qid > from > > kkk) group by qid; > > > > > > What use is that where statement? It just says to use all the rows in the > > table. > > > > > > -- > > Unhappiness is discouraged and will be corrected with kitten pictures. > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
