Confusing query


I have a table of 555 records. I need to return a subset of these records and I know I should get 544 back.

Basically I need all records but if two items have the same “order_ridden” value, then it should look at the “version” field (which will contain an integer, most likely 1, 2, 3 or 4) and pick the one with the highest value. I need to select all fields.

My current query is:

SELECT order_ridden, max(version) AS version
FROM coasters
GROUP BY order_ridden

This returns the correct number of results but ONLY returns the order_ridden and version fields. I need every field.

I am running PostgreSQL.

Any ideas on how I get write my query to return these results?

Kind regards,

SELECT coasters.*
  FROM ( SELECT order_ridden
              , MAX(version) AS max_version
           FROM coasters
             BY order_ridden ) AS m
  JOIN coasters
    ON coasters.order_ridden = m.order_ridden
   AND coasters.version = m.max_version

Hey Rudy!

Yes that works but I apologise, I forgot to mention an additional part. Some records do not have versions and therefore the fields are NULL. I need those records returning too.

try replacing the last line with

AND COALESCE(coasters.version,0) = COALESCE(m.max_version,0)

Absolutely spot on! Please can you explain how that line is actually working as I’m not quite sure I understand it?

explain it? you mean, explain how COALESCE works?

I’ve been looking into coalesce and as far as I can see it chooses the first non null value from the values you pass it? What I cannot understand is how it works by doing a comparison from one coalesce statement to another?

some records do not have versions and therefore the column values are NULL

so the MAX() of those column values will also be NULL

so to match the matching rows back to the NULL from the MAX() in the subquery, we match on zeros

if the rows do have a non-NULL value, then the MAX() will be non-NULL, so we match the matching rows on the column values, since COALESCE will pick those values first

Ok, thanks. So basically this is playing on the two facts that 0 == NULL and coalesce will pick the first non-NULL value ? correct?

no, sorry, no, not at all

0 is never equal to NULL

nothing is equal to NULL, not even another NULL

Ok, Just stepped throguh it again. Could you tell me if this sounds like it.

On coasters that have a NULL version value, the first values of the coalesce functions are NULL’s so it looks to the second values which show as 0 and in effect it is comparing 0 to 0 which matches.

Correct second time around?

by george i think you’ve got it

the additional nuance is that MAX() returns NULL when all of the values it’s trying to find the max of are NULL

Ok, I’ll try to keep everything in mind. Thankyou so much. It’s still confusing but i’ll keep running through it.

Thanks again!