T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

T-SQL

Author

Topic

pravin14u
Posting Yak Master

246 Posts

Posted - 2007-12-05 : 00:45:28

Hi All,

My table strcuture is as follows:
(CountryID, Country_Code, Country_Desc).

Two rows of the table are:
(1, IN, India),
(2, IN, Indian Sub)

Now When I make a join to this table on Country_Code, My rows gets doubled which I dont want.

I need to retrieve either India or Indian Sub(Any one is fine). Can you suggest any ideas?

Thanks,

Prakash.P

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-12-05 : 00:54:46

Join using this query


Select * From [YourTable] T1 
Where CountryID = (Select Max(CountryID) From [YourTable] T2 Where T2.Country_Code = T1.Country_Code)

Chirag

http://www.chirikworld.com

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-12-05 : 04:23:46

SELECT * FROM myTable t1
INNER JOIN (Select Max(CountryID) From myTable) t2 ON t2.CountryID = t1.CountryID

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-12-05 : 04:27:17

quote:

SELECT * FROM myTable t1
INNER JOIN (Select Max(CountryID) as CountryID From myTable) t2 ON t2.CountryID = t1.CountryID

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-12-05 : 04:41:11

[code]
SELECT * FROM myTable t1
INNER JOIN (Select Max(CountryID) as CountryID From myTable GROUP BY Country_Code ) t2 ON t2.CountryID = t1.CountryID
[/code]

Chirag

http://www.chirikworld.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources