Hi list,
i have a problem with creating a query and i hope somebody can give me
some hints. I have the following table
pid(varchar), crit(varchar), val1(varchar), val2(varchar),
iDate(timestamp)
where there are up to 63 million lines with 1500 distinct pids and
around 42000 distinct crits:
pid crit val1 val2 iDate
'yyy' 'aaa' 'b' 'c' someTime
'yyy' 'bbb' 'b' 'a' anotherTime
...
What i have to do is to export the table for which i have to query the
table with the following pattern: select val1, val2 from mytable where
pid='yyy' and crit='aaa'. But if i do this 63 million times, it just
takes too long. So, what i would like to do is to make a query where i
would get all val1 and val2 values for ONE pid and ALL crit. The problem
here is that all crit values have to be ordered by the iDate column. So
i tried to select all distinct crits ordered by iDate: select distinct
crit, iDate from mytable order by iDate. The problem here is that not
only the distinct crits are selected but also the distinct iDate values,
which in the end retrieves much more lines than there are distinct crit
values in the table.
I hope somebody can help me with this probably rather trivial problem.
Thanks!
Chris
