Search code examples
sqlsql-serversql-scripts

How to search duplicate records and order by columns from MSSQL Table without primary key?

I getting top 20 record from SQL Table
There is a column State in My MS SQL Table there are some numeric data,

In my case I skipped numeric records from C# function after I am getting duplicate results of City and Country,
for example 'Los Angeles','Venezuela' coming duplicates if I skipped state numeric data
How to remove/delete duplicate entries if the 'state' field contains numeric using SQL scripts

also I want to get top records from the Country 'United states' in Descending order
I wrote query as below:-

  Declare @sCity varchar(100),
    SELECT     Top 20  City,State,Country   
    FROM 
        [dbo].[Locations]  
    WHERE   
        City like  @sCity+'%'   
    ORDER BY    
            [dbo].[AllWorldLocations].Country DESC,         
            [dbo].[AllWorldLocations].STATE ASC, 
            [dbo].[AllWorldLocations].City ASC


my table is like that enter image description here


I want to get records descending order by country

Los Angeles             California   United States
Los Angeles             Texas        United States
Los Angeles Subdivision    Texas        United States
Solution

  • Find duplicates: http://petefreitag.com/item/169.cfm

    Filter numeric characters: http://msdn.microsoft.com/en-us/library/ms186272.aspx