Union of selects within a loop with postgresql

Question

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?

What do your indexes look like? You need the aggregate to get the same effect — dfb
– dfb, Commented Feb 19, 2015 at 15:41
Also, IN(ARRAY('red', 'blue') has multiple serious problem, please update your question to include your real queries. — pozs
– pozs, Commented Feb 19, 2015 at 15:45

Gordon Linoff · Accepted Answer · 2015-02-19 15:44:22Z

4

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;

answered Feb 19, 2015 at 15:44

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1