It gave me the syntax error! JP
________________________________ From: "Adler, Eliedaat" <ead...@nds.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Sunday, March 8, 2009 1:08:51 PM Subject: Re: [sqlite] select statement - Need help By side-effect the following statement should give those values: Select remoteId, hostName, max(lastUpdateTime) from (select * from table order by hostName, lastUpdateTime) Group by hostName having count(*) > 1 ; The outer select will return the last row processed by aggregate function max(lastUpdateTime) - i.e. the last row for each group. The internal select order guarantees that row will have max(lastUpdateTime) The more correct SQL would be something like: Select remoteId, t.hostname, lastUpdateTime from table, ( Select hostname, Max(lastUpdateTime) max_utime, count(*) cnt from table group by hostName) host_max where table.hostname = host_max.hostname and table.lastUpdateTime = max_utime and cnt > 1 ; Eli -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joanne Pham Sent: Sunday, March 08, 2009 9:45 PM To: General Discussion of SQLite Database Subject: [sqlite] select statement - Need help Hi All, I have the folowing table which has the following data for example: remoteId hostName lastUpdateTime .... 1 host1 199999 2 host1 111111 3 host2 222222 4 host3 333333 5 host4 499999 6 host4 444444 So if I ran this statement below: select * from table group by hostName having count(*) > 1; I got the following rows: 2 host1 111111 6 host4 444444 But I want the rows which have bigger lastUpdateTime if hostName has duplicate row. So I want to return: 1 host1 199999 2 host4 499999 Would like to have sql statement to return the rows above. Thanks, JP _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This e-mail is confidential, the property of NDS Ltd and intended for the addressee only. Any dissemination, copying or distribution of this message or any attachments by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, please immediately notify the postmas...@nds.com and destroy the original message. Messages sent to and from NDS may be monitored. NDS cannot guarantee any message delivery method is secure or error-free. Information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. We do not accept responsibility for any errors or omissions in this message and/or attachment that arise as a result of transmission. You should carry out your own virus checks before opening any attachment. Any views or opinions presented are solely those of the author and do not necessarily represent those of NDS. To protect the environment please do not print this e-mail unless necessary. NDS Limited Registered Office: One London Road, Staines,Middlesex TW18 4EX, United Kingdom. A company registered in England and Wales Registered no. 3080780 VAT no. GB 603 8808 40-00 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users