-- To print only the first value in a group of duplicates
-- akin to 'suppress if duplicated' option for fields in various reporting tools
SELECT CASE WHEN (SELECT COUNT(*) FROM pubs..authors a2
                    WHERE a2.au_lname <= a1.au_lname and a2.au_fname <= a1.au_fname and
                        a2.state = a1.state) = 1
            THEN a1.State
        ELSE ''
       END as "State Field Header",
        a1.au_lname, a1.au_fname, a1.state
FROM pubs..authors a1
ORDER BY a1.state, a1.au_lname, a1.au_fname

This page was last updated on May 01, 2006 04:28 PM.