Distinct for More than one column

Question

Distinct for More than one column

msreddyn

SSC-Addicted

Points: 484
More actions
September 15, 2008 at 3:43 am

#192758

Hi friends,
I have a table with two columns FromLocation and ToLocation ,these columns may contain same data or also distinct data . now i want query
data such that my output should contains all distinct values from both the columns .
is there any simply way for this.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SELECT DISTINCT FromLocation, ToLocation FROM ...

will get you all of the unique combinations of the two. Is that what you want, or are you looking for just the values of the two, regardless of any relationship between the two?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

SELECTCol,

Col2,

Col3,

Col4,

Col5

FROM(

SELECTCol,

Col2,

Col3,

Col4,

Col5,

ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col3 DESC) AS RecID

FROMTable1

) AS d

WHERERecID = 1

N 56°04'39.16"
E 12°55'05.25"

thegreatsamarth SSChasing Mays Points: 608 More actions · Answer 3

msreddyn (9/15/2008)
Hi friends,
I have a table with two columns FromLocation and ToLocation ,these columns may contain same data or also distinct data . now i want query
data such that my output should contains all distinct values from both the columns .
is there any simply way for this.
Thanks

HI,

The output of below query 'll contain all distinct values from both the columns:

SELECT DISTINCT TAB.FromLocation FROM

(

SELECT DISTINCT FromLocation FROM tablename

UNION

SELECT DISTINCT ToLocation FROM tablename

) TAB

I think this is all u r looking for.

-Samarth

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

I hope you meant

SELECT FromLocation AS theLocation FROM tablename

UNION

SELECT ToLocation FROM tablename

N 56°04'39.16"
E 12°55'05.25"

msreddyn SSC-Addicted Points: 484 More actions · Answer 5

Thanks Samrath ,

thats what exactly i am looking for

Thank you very much

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 6

msreddyn (9/15/2008)
Thanks Samrath ,
thats what exactly i am looking for
Thank you very much

True, but use it the way Peso posted it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning