retrieving unique values

Hello,

Having a bit of writer's block here. I'm trying to retrieve records from a table, but if the any of the columns have duplicate info, ignore all the duplicates and just return the values once.

This is for a maintenance activity which I have been handed. I understand perhaps there are some issues with the tables but I cannot change how things have been developed before my tenure.

Consider the following table:

pk street_number direction street_name

001  01697                       ALLEN WY
002  01697                       ALLEN WY
003  06020                       WEBER ST
004  00142                       RAVEN DR
005  00102           S.          WEBER ST
006  00102           S.          WEBER ST

The results which I'm looking to return from my SQL statement are:

01697                       ALLEN WY
06020                       WEBER ST
00142                       RAVEN DR
00102           S.          WEBER ST

01697 ALLEN WY and 00102 S. WEBER ST are duplicates; so they only need to appear once.

DISTINCT won't work because then I'd eliminate recurrences of the same street name even though there may be more than one number for the street (06020 and 00102 for WEBER ST).

I thought of grouping but then I quickly realized, I'm not using any group functions (yet). I want the results to appear just like this:

01697                       ALLEN WY
06020                       WEBER ST
00142                       RAVEN DR
00102           S.          WEBER ST

There's nothing like avg, main, max, etc going on where I could group by.

Suggestions are greatly appreciated.

Thanks! Received on Thu Mar 15 2007 - 16:05:51 CDT