one record from each group?

Question

Say I have a table like this:

x1 | y1 | x2 | y2 | area | color 

 5 |  0 |  5 |  0 |    1 | r
 5 |  0 |  6 |  0 |    2 | g
 5 |  1 |  5 |  0 |    2 | b
 5 |  1 |  5 |  1 |    2 | r
 5 |  2 |  5 |  0 |    3 | g
 5 |  2 |  5 |  1 |    3 | b

How can I construct an SQL query so that the resulting table has one of r, g, b (each having the maximum area), but also preserving the coordinates of this record? I tried MAX(AREA) and GROUP BY color, but that gives me records with maximum area for r, g, b but not together with their coordinates.

I am using PostgreSQL Sample output (for the one above), should be something like:

 5 |  1 |  5 |  1 |    2 | r
 5 |  2 |  5 |  0 |    3 | g
 5 |  2 |  5 |  1 |    3 | b

Basically it should return 4th, 5th, and 6th record.

Answer 1

Your question is a little unclear, but I guess you want this:

SELECT T2.* FROM (
    SELECT color, max(area) AS area
    FROM table1
    GROUP BY color) AS T1
JOIN table1 AS T2
ON T1.area = T2.area AND T1.color = T2.color

Note that it assumes that the maximum area is unique, otherwise you will get multiple rows.

Output for your data:

x1 | x2 | y1 | y2 | area | color
 5 |  1 |  5 |  1 |    2 |     r
 5 |  2 |  5 |  0 |    3 |     g
 5 |  2 |  5 |  1 |    3 |     b

If you want a better answer, please clarify all the questions I have posted as comments (some of them are hidden).

Answer 2

select *, max(area), color from T group by color;