Re: retrieving unique values

On 15 Mar, 21:05, "Adam Sandler" <cor..._at_excite.com> wrote:
> 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!

It seems like DISTINCT would give exactly the result you asked for:

SELECT DISTINCT street_number, direction, street_name FROM tbl;

GROUP BY too:

SELECT street_number, direction, street_name FROM tbl
GROUP BY street_number, direction, street_name;

--
David Portas