"Lonni J Friedman" <netllama@gmail.com> writes:
> I tried to call DISTINCT ON, with the following query:
> SELECT DISTINCT ON(date_created, cudacode) id,current_status FROM
> cudaapps WHERE (SELECT now() - interval '24 hours' <
> date_created::timestamp)='t' ORDER BY date_created;
No, you're not grokking how to use DISTINCT ON. The DISTINCT part
specifies what rows you want to group together --- here, all the ones
with the same cudacode --- and then ORDER BY has to list all those same
columns *followed by* the ordering condition that determines which row
you want out of the group. What I think you want is
SELECT DISTINCT ON (cudacode) id,current_status
FROM cudaapps
WHERE (SELECT now() - interval '24 hours' < date_created::timestamp)='t'
ORDER BY cudacode, date_created DESC;
which gives you the latest current_status for each cudacode, and then
you filter the uninteresting rows in an outer select:
SELECT id
FROM
(SELECT DISTINCT ON (cudacode) id,current_status
FROM cudaapps
WHERE (SELECT now() - interval '24 hours' < date_created::timestamp)='t'
ORDER BY cudacode, date_created DESC) ss
WHERE current_status = 'FAILED';
>> BTW, why are you writing the timestamp filter condition in such a
>> bizarre way?
> I'm doing this because date_created is of type char(20) rather than a
> normal/expected date datatype. I know, its dumb.
Well, so you have to have the cast, but I'd still think that
WHERE (now() - interval '24 hours') < date_created::timestamptz
would be the best way to express it. The sub-SELECT and the comparison
to 't' are just obscurantism.
Another issue here is whether the date format was chosen to ensure that
textual sorting of the values would give the same result as datewise
sorting. You might get some fairly bizarre misbehavior if the data is
sloppy about spaces instead of zero-fill, for instance. Sometimes it's
worth biting the bullet and fixing the column type ...
regards, tom lane