| Author: Tejinder Singh Barnala 24 Dec 2008 | Member Level: Gold | Rating:  Points: 3 |
shanthy, you are going in wrong direction
you are saying that you have a table
pkid country
1 india
2 india
3 newzeland
and you want result
1, india
3, newzeland
if i am right than
you need not to use distinct, u have to use group by
like
select min(ID), country from table
group by country
or
select max(ID), country from table
group by country
hope it will help u
Many Thanks
Tejinder Singh Barnala
/*I have the simplest tastes. I am always satisfied with the best*/
|
| Author: Durga Prasad 24 Dec 2008 | Member Level: Gold | Rating: Points: 1 |
Select DISTINCT countryID ,ID,CountryName from tbl_Country
|
| Author: Babu Akkandi 24 Dec 2008 | Member Level: Diamond | Rating: Points: 2 |
Hi shanthy,
Use this Query,
SELECT distinct
[ID],
country_id,
country_name
FROM
Table1
WHERE
[ID] NOT IN
(SELECT MAX([ID]) FROM Table1 AS P WHERE Table1.country_id= P.country_id)
Use Min or Max, whatever you want, in sub Query
Hope it helps
Thanks and Regards,
Babu Akkandi
Microsoft Technology
|
| Author: Sriman N Vangala 24 Dec 2008 | Member Level: Diamond | Rating: Points: 5 |
Here the table itself not properly designed.
In the Table tbl_Country, You have three columns
Id,countryID,CountryName where id is primary key. As you have the Id as primary key there is no need of countryID again as a separate column. Id itself can be treated as the countryID And as the tbl_Country table is the master table, it won't contain the duplicate values.
hence first change the design of the table tbl_Country as
tbl_Country(ID,name,...)
or
tbl_Country(CountryID,name,...)
then you can have the values as below:
CountryID Name
1 India
2 US
3 China
Now you can have only the distinct country values in the table and you can get it using the query
Select countryID,Name from tbl_Country
and there is no need of using Distince keyword in this case
|
| Author: Pradeep M 24 Dec 2008 | Member Level: Silver | Rating: Points: 1 |
Below code displays the results with distinct values in each row
Select distinct id, countryID,Name from tbl_Country
|