Re: issue with query language

[asgallant]

The query language is insufficiently sophisticated to do what you want.  You will have to filter out the values you want using the DataTable methods, and perhaps the group function (google.visualization.data.group).  Use a DataView to present the filtered data to your visualization(s).



On Sunday, September 23, 2012 9:16:37 PM UTC-4, lorenzo vigentini wrote:

hi everyone,

i'm having some troubles with a query and i can't seem to be able to figure out how to make it work. so here is the problem.

i have a table in a google spreadsheet with the following columns (they should be straightforward and in the query script i use the column values):

A:Timestamp

B:ID

C:sessionDate

D:First Name

E:Surname

F:Email

G:EntryNo

apart for the entryNo (which is an incremental number for each ID, which means that for every ID i could have multiple entries with different sessionDates) they are all strings. 

what i would like to pull out is a list of all IDs for a given sessionDate, but filter only the latest entryNo, which represents the latest entry for a given ID.can anyone suggest the right way of doing it?

so far i managed to get these;

if i use a query like the following i can get the latest entry for a given ID: 

SELECT * where B = 'myID' order by G desc limit 1

the following query gives me the list of all IDs in a particular session and sorted by last name (however also counts those where the entryNo is not the highest value):

SELECT * where C = 'mySession' order by E asc 

in order query languages i would try some joins, but with the google query script i just can't figure it out! any help/suggestion will be very much appreciated,

many thanks

lorenzo

[lorenzo vigentini]

hi asgallant, thanks for the suggestion; pointing me directly to the bits of relevant docs was very useful indeed!

although the query language itself is rather limited, using the dataview stuff allowed me to solve the problem. for those reading the thread, basically creating a view reduces the data and then using the filtering methods it is possible to display only the sub-query