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?
SELECT coasters.*
FROM ( SELECT order_ridden
, MAX(version) AS max_version
FROM coasters
GROUP
BY order_ridden ) AS m
INNER
JOIN coasters
ON coasters.order_ridden = m.order_ridden
AND coasters.version = m.max_version
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.
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, 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.