Ignore day duplicates from timestamps in sqlite table selection -

i have sqlite database contains timstamps , tick data. timestamps contain date , time. 'end-of-day' analysis want leave out timestamps duplicates same date. doesn't matter database row of date duplicates selected.

the table looks this:

create table stockquotes     (`timestamps` varchar(19), `open` float) ;  insert stockquotes     (`timestamps`, `open`) values     ('2010-09-16 13:16:22', 33.63),     ('2010-09-17 13:16:22', 33.53),     ('2010-09-20 11:26:30', 33.46),     ('2010-09-20 13:16:22', 33.46),     ('2010-09-21 11:26:30', 33.76),     ('2010-09-22 11:26:30', 33.56),     ('2010-09-23 11:26:30', 33.86),     ('2010-09-23 13:26:30', 33.86) ;  

my desired result is:

timestamps              open 2010-09-16 13:16:22     33.63 2010-09-17 13:16:22     33.53 2010-09-20 11:26:30     33.46 2010-09-21 11:26:30     33.76 2010-09-22 11:26:30     33.56 2010-09-23 11:26:30     33.86 

or

timestamps              open 2010-09-16 13:16:22     33.63 2010-09-17 13:16:22     33.53 2010-09-20 13:16:22     33.46 2010-09-21 11:26:30     33.76 2010-09-22 11:26:30     33.56 2010-09-23 13:26:30     33.86 

i can find duplicates query:

select timestamps, count(subts) countsub (   select timestamps,substr (timestamps,1,10) subts, open   stockquotes ) group subts having (count(subts)>1); 

but not work:

select * stockquotes timestamps not in ( select timestamps, count(subts) countsub (   select timestamps,substr (timestamps,1,10) subts, open   stockquotes ) group subts having (count(subts)>1) ); 

what mistake?

the mistake you're filtering out rows duplicates, both copies missing.

to 1 output row (potentially) multiple input rows, use group by:

select max(timestamps) timestamps,        open stockquotes group date(timestamps); 

the max() ensures latest row in group.