October 11, 2008 at 2:18 pm
How can I return values only once even though they are in the database multiple times.
for example, in one table, I have the following cities under the column METRO:
baltimore, baltimore, baltimore, philadelphia, philadelphia, chicago, chicago, chicago, chicago....
I would like to have a command that returns only one instance of each city, but so far it is returning all of them, therefore creating duplicate returns.
I just need it to return baltimore, chicago, philadelphia in the return or any other city that is in the table multiple times.
Any help would be appreciated.
Thanks a lot. This site is a great resource.
October 11, 2008 at 3:00 pm
As long as it is the only column in the query meaning no other columns will cause the cities to duplicate you should be able to use the Distinct key word.
Select Distinct Metro From YourTableName
October 13, 2008 at 9:06 am
hi,
If you need to avoid duplicate then it really depends on how many columns do you have in the query. If you have duplicate record with same city then you can avoid by GROUP BY statement. You can even use Distinct.
If you have primary key as ID in your table then following code will work.
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicatevalueColumn1, DuplicateValueColumn2,
DuplicateValueColumn2)
I hope it helps.
Vijay
October 14, 2008 at 8:57 am
Nice input dva2007, the group by clause is also a good way to remove duplicate values. Along with distinct.
October 14, 2008 at 9:00 am
You weren't terribly clear in the query you are working with, but if the columns you are trying to get a distinct list span multiple tables, you could do something like this:
select distinct City from
(
select distinct City from MyTable1
union all
select distinct City from MyTable2
) a
October 15, 2008 at 2:11 pm
How about?
select distinct City from MyTable1
union
select distinct City from MyTable2
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply