CODEWITHSUNDEEP
sqlpostgresql
Paul Fournel
Paul Fournel

Reputation: 11207

Union of selects within a loop with postgresql

I have a table like this

--------------------
| color |   date   |
--------------------
| red   | 01/01/10 |
| blue  | 01/07/10 |
| ...              |

I want to select the maximum date of every type. The easy way has (realy) bad performance. ie: 

   SELECT color, MAX(date) 
     FROM myTable
    WHERE color = ANY(ARRAY('red', 'blue'))
 GROUP BY date;

What has good performance the following query. I think this is because of the index on date.

    SELECT color, date
      FROM myTable
     WHERE color='red'
  ORDER BY date
     LIMIT 1

the problem is that I do not have the same result. I would like to find a way to use the 2nd version to have the same result as the first query.

Perhaps I could use a Loop and select the union of each result. Is this even possible?

Any other idea is welcomed?

Upvotes: 0

Views: 913

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Why do you need the array keyword? Doesn't this do what you want for the first query?

SELECT date, MAX(color) 
FROM myTable
WHERE color IN ('red', 'blue')
GROUP BY date;

But you seem to want:

select color, max(date)
from mytable
group by color;

Upvotes: 4

Related Questions