Hello all,

I want to select complete row from a table:


+-------+------------+------------+
| group | date | price |
+-------+------------+------------+
| 1 | 2009-03-01 | 300.00 |
| 1 | 2009-03-01 | 299.00 |
| 2 | 2009-03-02 | 302.00 |
| 2 | 2009-03-02 | 298.00 |
| 2 | 2009-03-14 | 300.00 |
+-------+------------+------------+


FOR EACH group SELECT * FROM table ORDER BY date, price LIMIT 1;

The problem is because I need sorting on two columns.

I reviewed "groupwise" recipes, but there are simpler examples.



Cheers
Artur

Some (untested) alternatives:

SELECT "group", "date", MAX(price)
FROM thistable
WHERE ("group", "date") IN (SELECT "group", MAX("date") FROM thistable GROUP BY "group")
GROUP BY "group", "date"

The following feature outside Core SQL-99 is used:
F641, "Row and table constructors"



SELECT "group", "date", MAX(price)
FROM thistable AS outmst
WHERE "date" = (SELECT MAX("date") FROM thistable WHERE "group" = outmst."group")
GROUP BY "group", "date"


Core SQL-99.


Note that both GROUP and DATE are reserved words in ANSI/ISO SQL. It's a good idea to double quote those column names to avoid future problems.

Many thanks. Results are as I wanted.
Probably I need to add some idexes. Table is so big, that on 2GHz didn't finish in 10 minutes.


Thanks
Artur

Careful with the quoting. As written by JarlH, you must have created the table using lower-case identifiers. The quoting turns off the implicit conversion to upper case.

Yet another approach is this:

I;m sorry - I made a mess using such names in the example. In my database the names are different (pure Polish) according to company policy.
.
Thanks for the example, I'll check it tomorrow.
.
Today I added new missing index, but I didn't gain gratifying performance. Still working on it.



Thanks
Artur

What was the index you added? And what SQL are you using?

pootle flump ur codings are working excelent.