An interesting query to write

Question

Post reply

An interesting query to write

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
September 14, 2009 at 6:24 am

#96004

Hi
I have a table with 2 columns: groupID,CountryName
it has data something like this:
1 Nepal
1 US
1 UK
2 INDIA
2 GERMANY
2 CHINA
Now I want a query that retuns me pares something like this:
CountryName1 CountryName2
Nepal US
Nepal UK
US UK
INDIA GERMANY
INDIA CHINA
CHINA GERMANY
I mean it has to retun unique pairs. The pair can be US NEPAL instead of NEPAL US but there should not be 2 entries like US NEPAL and NEPAL US. I reied self join and removed records like NEPAL NEPAL but I couldn't remove duplicate pairs.
scripts are below for your ease:
create table countries(GroupID int, CountryName varchar(20))
INSERT INTO countries values(1,'NEPAL')
INSERT INTO countries values(1,'US')
INSERT INTO countries values(1,'UK')
INSERT INTO countries values(2,'INDIA')
INSERT INTO countries values(2,'GERMANY')
INSERT INTO countries values(2,'CHINA')
HELP............
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 11 posts - 1 through 10 (of 10 total)

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

Will this work?

;WITH cte AS

(

SELECT c1.countryName CountryName1, c2.countryName CountryName2, ROW_NUMBER() OVER (partition by checksum(c1.countryName)+checksum(c2.countryName) ORDER BY c1.groupid) rowNum

FROM countries c1

FULL OUTER JOIN countries c2 ON c1.groupID = c2.groupID AND c1.countryName c2.countryName

)

SELECT CountryName1, CountryName2 FROM cte

WHERE rowNum = 1

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

works like magic...ur awesome.........

So All i was looking for here was checksum...

thanks fo rmaking me learn this new function

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 3

This query also returns your expected results I think:

SELECT A.CountryName AS CountryName1, B.CountryName AS CountryName2

FROM Countries A INNER JOIN Countries B

ON (A.GroupId = B.GroupId AND A.CountryName < B.CountryName)

matt6288 SSCertifiable Points: 5058 More actions · Answer 4

I just noticed a problem in my script also. You will probably need to change the line to read: partition by checksum(c1.countryName+c2.countryName) as the other way could cause an integer overflow.

Also, andrew's solution looks like it will work if you always have at least 2 entries per groupID. If that isn't that case then you can change his to be:

SELECT DISTINCT A.CountryName AS CountryName1, B.CountryName AS CountryName2

FROM Countries A LEFT outer JOIN Countries B

ON (A.GroupId = B.GroupId AND A.CountryName < B.CountryName)

I think that will get the correct results also.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 5

My sincere thanks to both you guys for sparing time to look into it.........

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 6

My sincere thanks to both you guys for sparing time to look into it.........

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 7

Curious what the application of this would be, since last time I checked, Nepal wasn't in the US, nor was the US in the UK or vice versa?

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 8

Single game play-offs for instance?

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 9

Ah! Thanks Jan, slow day for me 😛

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

ChiragNS One Orange Chip Points: 26137 More actions · Answer 10

one more way using cross join

select c2.countryname, c.countryname from country c

cross join country c2

where c.groupid= c2.groupid and c.country c2.country

order by c.groupid

"Keep Trying"