Here is an example of the table I am working withWhat I would like to achieve is to select the most recent row where the type is not ‘NONE’ unless ‘NONE’ is the only type available for that id.
| id | date | type |
|---|---|---|
| 123 | 01-01-2021 | NONE |
| 123 | 12-31-2021 | NONE |
| 123 | 01-01-2021 | METAL |
| 123 | 12-31-2021 | METAL |
From the example table above I would expect the query to return this
| id | date | type |
|---|---|---|
| 123 | 12-31-2021 | METAL |
If the table were to only contain types of “NONE” such as this example…
| id | date | type |
|---|---|---|
| 123 | 01-01-2021 | NONE |
| 123 | 12-31-2021 | NONE |
| 123 | 01-01-2021 | NONE |
| 123 | 12-31-2021 | NONE |
Then I would expect the result set to be..
| id | date | type |
|---|---|---|
| 123 | 12-31-2021 | NONE |
I’ve tried a plethora of different ways to do this but my current attempt looked something like thisIt works when there’s only one ID in the table but not for when I try to select a row for every specific ID in the table.
SELECT DISTINCT ON (id),
date,
type
FROM
example_table
WHERE
CASE
WHEN
( SELECT
COUNT(*)
FROM
example_table t
WHERE
t.type <> 'NONE'
AND t.id = example_table.id)
<> 0
THEN type <> 'NONE'
ELSE 1=1
END
ORDER BY
id, date DESC
