Display rows with the highest date attribute for the ones that share the same first name

Question

I want to display the record with the highest slot date for the records that share the same first name. How would I proceed on doing that?

Answer 1

Use can use the ROW_NUMBER windowed function to accomplish this. See: http://www.postgresql.org/docs/9.3/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS and http://www.postgresql.org/docs/9.3/static/tutorial-window.html

SELECT * 
FROM 
(
SELECT E.firstname
    , S.category 
    , S.showname 
    , O.slotdate
    , ROW_NUMBER() over(PARTITION BY E.firstname ORDER BY O.slotdate DESC) as rowNum
FROM hostshows H , Shows S , Host E ,timeslot O
WHERE S.shownumber = E.shownumber
AND H.empnum = E.empnum
AND O.shownumber = H.shownumber
) t
WHERE rowNum = 1
;

Answer 2

This is a textbook example for DISTINCT ON:

SELECT DISTINCT ON (e.firstname)
       e.firstname, s.category, s.showname, o.slotdate
FROM   shows     s 
JOIN   host      e USING (shownumber)
JOIN   timeslot  o USING (shownumber)
JOIN   hostshows h USING (empnum)
ORDER  BY e.firstname, o.slotdate DESC;

Detailed explanation:
Select first row in each GROUP BY group?