This content has been marked as final. Show 2 replies
Here's one way:
If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.
SELECT DISTINCT TO_CHAR ( MIN (mem_date_of_birth) OVER () , 'MM/DD/YYYY' ) AS oldest_birth , FIRST_VALUE (mem_first_name || ' ' || mem_last_name) OVER ( ORDER BY mem_date_of_birth , primary_key ) AS oldest_member TO_CHAR ( MAX (mem_date_of_birth) OVER () , 'MM/DD/YYYY' ) AS newest_birth , FIRST_VALUE (mem_first_name || ' ' || mem_last_name) OVER ( ORDER BY mem_date_of_birth DESC , primary_key ) AS newest_member FROM mem_member;
It looks like you had the basic idea, but you were comparing strings, such as '01/17/2013' instead of DATEs, and the string '12/01/1999' comes after the string '01/17/2013;, since '1' comes after '0'. You need to convert the DATEs to strings after the MIN or MAX has been found. You would want to do this even if the strings sorted in the right order. It's more efficient to call TO_CHAR just once, on the MIN or MAX, rather than call it on every row.
What output would you want if there is a tie for the oldest or newest? The query above picks the contender with the lowest value of primary_key in each case. You can substitute any columns or expressions you want for primary_key.
The error in your "modified query" seems to be
MIN (TO_CHAR ( MEM_date_of_birth ....