Re: [sqlite] Using non-aggregate columns in group by (with an additional issue)
A very minor change on your query is all that is needed. select max(x.timestamp),x.value,x.person from table as x, (select person,max(value) as maxvalue from table group by person order by maxvalue desc) as y where x.person = y.person and x.value = y.maxvalue group by x.person, x.value I don't know how this compares in terms of performance with Igor's solution. --David Garfield On Fri, Jan 13, 2012 at 06:07, Dilip Ranganathan wrote: > I have a table that looks like something like this: > >timestampvalue person >=== >2010-01-12 00:00:00 33 emp1 >2010-01-12 11:00:00 22 emp1 >2010-01-12 09:00:00 16 emp2 >2010-01-12 08:00:00 16 emp2 >2010-01-12 12:12:00 45 emp3 >2010-01-12 13:44:00 64 emp4 >2010-01-12 06:00:00 33 emp1 >2010-01-12 15:00:00 12 emp5 > > I wanted to find the maximum value associated with each person. The obvious > query was: > > select person,max(value) from table group by person > > Now I wanted to include the timestamp associated with each max(value). I > could not use timestamp column in the above query because as everyone > knows, it won't appear in the group by clause. So I wrote this instead: > > select x.timestamp,x.value,x.person from table as x, > (select person,max(value) as maxvalue from table group by person order by > maxvalue desc) as y > where x.person = y.person > and x.value = y.maxvalue > > This works -- to an extent. I now see: > >timestampvalue person >=== >2010-01-12 13:44:00 64 emp4 >2010-01-12 12:12:00 45 emp3 >2010-01-12 06:00:00 33 emp1 >2010-01-12 00:00:00 33 emp1 >2010-01-12 08:00:00 16 emp2 >2010-01-12 09:00:00 16 emp2 >2010-01-12 15:00:00 12 emp5 > > The problem is now I get all the entries for emp1 and emp2 that ends up > with the same max(value). > > Suppose among emp1 and emp2, I only want to see the entry with the latest > timestamp. IOW, I want this: > >timestampvalue person >=== >2010-01-12 13:44:00 64 emp4 >2010-01-12 12:12:00 45 emp3 >2010-01-12 06:00:00 33 emp1 >2010-01-12 09:00:00 16 emp2 >2010-01-12 15:00:00 12 emp5 > > What kind of query would I have to write? Is it possible to extend the > nested query I wrote to achieve what I want or does one have to rewrite > everything from the scratch? > > If its important, timestamps are actually stored as julian days. I use the > datetime() function to convert them back to a string representation in > every query. > ___ > 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] Using non-aggregate columns in group by (with an additional issue)
On Fri, Jan 13, 2012 at 6:22 AM, Simon Slavin wrote: > > On 13 Jan 2012, at 11:07am, Dilip Ranganathan wrote: > > > I have a table that looks like something like this: > > > >timestampvalue person > >=== > >2010-01-12 00:00:00 33 emp1 > >2010-01-12 11:00:00 22 emp1 > >2010-01-12 09:00:00 16 emp2 > >2010-01-12 08:00:00 16 emp2 > >2010-01-12 12:12:00 45 emp3 > >2010-01-12 13:44:00 64 emp4 > >2010-01-12 06:00:00 33 emp1 > >2010-01-12 15:00:00 12 emp5 > > > > I wanted to find the maximum value associated with each person. > > Do you have another table with your persons in it ? In other words, can > you do > > Unfortunately No. I am stuck with that one table. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using non-aggregate columns in group by (with an additional issue)
On 13-01-2012 12:07, Dilip Ranganathan wrote: > I have a table that looks like something like this: > > timestampvalue person > === > 2010-01-12 00:00:00 33 emp1 > 2010-01-12 11:00:00 22 emp1 > 2010-01-12 09:00:00 16 emp2 > 2010-01-12 08:00:00 16 emp2 > 2010-01-12 12:12:00 45 emp3 > 2010-01-12 13:44:00 64 emp4 > 2010-01-12 06:00:00 33 emp1 > 2010-01-12 15:00:00 12 emp5 > > I wanted to find the maximum value associated with each person. The obvious > query was: > > select person,max(value) from table group by person > > Now I wanted to include the timestamp associated with each max(value). I > could not use timestamp column in the above query because as everyone > knows, it won't appear in the group by clause. So I wrote this instead: > > select x.timestamp,x.value,x.person from table as x, > (select person,max(value) as maxvalue from table group by person order by > maxvalue desc) as y > where x.person = y.person > and x.value = y.maxvalue > > This works -- to an extent. I now see: Because it only works 'to an extent', try: select t1.person, max(t1.value), t2.timestamp from test t1 left join test t2 on t1.person=t2.person and t.1value=t2.value group by t1.person; -- Luuk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using non-aggregate columns in group by (with an additional issue)
On 13 Jan 2012, at 11:07am, Dilip Ranganathan wrote: > I have a table that looks like something like this: > >timestampvalue person >=== >2010-01-12 00:00:00 33 emp1 >2010-01-12 11:00:00 22 emp1 >2010-01-12 09:00:00 16 emp2 >2010-01-12 08:00:00 16 emp2 >2010-01-12 12:12:00 45 emp3 >2010-01-12 13:44:00 64 emp4 >2010-01-12 06:00:00 33 emp1 >2010-01-12 15:00:00 12 emp5 > > I wanted to find the maximum value associated with each person. Do you have another table with your persons in it ? In other words, can you do SELECT code FROM people ORDER BY code ? If so, that gives you a list of people to start from. Then you can do something like SELECT people.code, max(scores.score) FROM people JOIN scores on scores.person = person.code ORDER BY people.code Note: the above is off the top of my head and untested. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using non-aggregate columns in group by (with an additional issue)
I have a table that looks like something like this: timestampvalue person === 2010-01-12 00:00:00 33 emp1 2010-01-12 11:00:00 22 emp1 2010-01-12 09:00:00 16 emp2 2010-01-12 08:00:00 16 emp2 2010-01-12 12:12:00 45 emp3 2010-01-12 13:44:00 64 emp4 2010-01-12 06:00:00 33 emp1 2010-01-12 15:00:00 12 emp5 I wanted to find the maximum value associated with each person. The obvious query was: select person,max(value) from table group by person Now I wanted to include the timestamp associated with each max(value). I could not use timestamp column in the above query because as everyone knows, it won't appear in the group by clause. So I wrote this instead: select x.timestamp,x.value,x.person from table as x, (select person,max(value) as maxvalue from table group by person order by maxvalue desc) as y where x.person = y.person and x.value = y.maxvalue This works -- to an extent. I now see: timestampvalue person === 2010-01-12 13:44:00 64 emp4 2010-01-12 12:12:00 45 emp3 2010-01-12 06:00:00 33 emp1 2010-01-12 00:00:00 33 emp1 2010-01-12 08:00:00 16 emp2 2010-01-12 09:00:00 16 emp2 2010-01-12 15:00:00 12 emp5 The problem is now I get all the entries for emp1 and emp2 that ends up with the same max(value). Suppose among emp1 and emp2, I only want to see the entry with the latest timestamp. IOW, I want this: timestampvalue person === 2010-01-12 13:44:00 64 emp4 2010-01-12 12:12:00 45 emp3 2010-01-12 06:00:00 33 emp1 2010-01-12 09:00:00 16 emp2 2010-01-12 15:00:00 12 emp5 What kind of query would I have to write? Is it possible to extend the nested query I wrote to achieve what I want or does one have to rewrite everything from the scratch? If its important, timestamps are actually stored as julian days. I use the datetime() function to convert them back to a string representation in every query. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
