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
