Re: [sqlite] select statement - Need help
"Joanne Pham" wrote in message news:936179.87380...@web90307.mail.mud.yahoo.com > I have the folowing table which has the following data for example: > remoteId hostName lastUpdateTime > > So if I ran this statement below: > select * from table group by hostName having count(*) > 1; > I got the following rows: > 2 host1 11 > 6 host4 44 > > But I want the rows which have bigger lastUpdateTime if hostName has > duplicate row. select * from myTable t1 where lastUpdateTime = (select max(lastUpdateTime) from myTable t2 where t1.hostName = t2.hostName group by t2.hostName having count(*) > 1); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select statement - Need help
Thanks! I worked! Select remoteId, table.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 ; t.hostName should be table.hostName Thanks a lot Adler for the help. JP From: Joanne Pham To: General Discussion of SQLite Database Sent: Sunday, March 8, 2009 1:44:01 PM Subject: Re: [sqlite] select statement - Need help It gave me the syntax error! JP From: "Adler, Eliedaat" To: General Discussion of SQLite Database 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 19 2 host1 11 3 host2 22 4 host3 33 5 host4 49 6 host4 44 So if I ran this statement below: select * from table group by hostName having count(*) > 1; I got the following rows: 2 host1 11 6 host4 44 But I want the rows which have bigger lastUpdateTime if hostName has duplicate row. So I want to return: 1 host1 19 2 host4 49 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select statement - Need help
It gave me the syntax error! JP From: "Adler, Eliedaat" To: General Discussion of SQLite Database 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 19 2 host1 11 3 host2 22 4 host3 33 5 host4 49 6 host4 44 So if I ran this statement below: select * from table group by hostName having count(*) > 1; I got the following rows: 2 host1 11 6 host4 44 But I want the rows which have bigger lastUpdateTime if hostName has duplicate row. So I want to return: 1 host1 19 2 host4 49 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
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 tablegroup 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 hostNamelastUpdateTime 1host119 2 host111 3host222 4host333 5host449 6host444 So if I ran this statement below: select * from table group by hostName having count(*) > 1; I got the following rows: 2 host1 11 6 host4 44 But I want the rows which have bigger lastUpdateTime if hostName has duplicate row. So I want to return: 1 host1 19 2 host4 49 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] select statement - Need help
Hi All, I have the folowing table which has the following data for example: remoteId hostName lastUpdateTime 1 host1 19 2 host111 3host2 22 4 host3 33 5 host4 49 6 host4 44 So if I ran this statement below: select * from table group by hostName having count(*) > 1; I got the following rows: 2 host1 11 6 host4 44 But I want the rows which have bigger lastUpdateTime if hostName has duplicate row. So I want to return: 1 host1 19 2 host4 49 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
