I have a table called table1 it has fields Id Cityname records 1 - miami 2 - miami 3 - miami 4 - orlando 5 - orlando 6 - miami I want to do a query on the records it should return only distinct city with ID iam using this select distinct cityname,id from table1 but the results return all the records. I want city name to be distinct at the same time also give the id for the cityname how to solve this can some one help
Well, really it depends on which id you want returned against each city - but... Select Cityname,Min(Id) From table1 Group by Cityname Should give you... Miami,1 Orlando,4 Coversely... Select Cityname,Max(Id) From table1 Group by Cityname Should give you... Miami,6 Orlando,5 But, if you ask me, it's a pretty silly table structure - you're breaking every normalisation rule and sooner or later, it's gonna bite you.
If those two fields are the only fields in the table I'd put a unique constraint on Cityname. They there would be no problem with your original query.