CODEWITHSUNDEEP
sqlsql-server
Pankaj Saha
Pankaj Saha

Reputation: 151

Select distinct records order by clause

I have a table 

Table1
(
  id bigint
  , name nvarchar(50)
)

This table can have same name with different case name.

Insert into Table1
(Select 1, 'Ram')
(Select 2, 'Ram')
(Select 3, 'Ram')
(Select 4, 'Ram')
(Select 5, 'ra')
(Select 6, 'ram')
(Select 7, 'RAM')
(Select 8, 'RAM')

Now I need to select all the distinct name by case and have the minimum id number. The result should be

1, Ram
6, ram
7, RAM

Upvotes: 0

Views: 145

Answers (3)

John Woo
John Woo

Reputation: 263723

SELECT MIN(ID) ID, name COLLATE Latin1_General_CS_AS Name
FROM   table1
WHERE  name <> 'ra'
GROUP  BY name COLLATE Latin1_General_CS_AS
ORDER  BY ID

Upvotes: 5

Sharad
Sharad

Reputation: 743

select min(id),name  from Table1group by UPPER(name) ASC

Upvotes: 0

Ivan Golović
Ivan Golović

Reputation: 8832

You can use this:

SELECT  t.id
        , t.name
FROM    (
        SELECT  ROW_NUMBER() OVER (PARTITION BY name COLLATE Latin1_General_CS_AI ORDER BY id) num
                ,*
        FROM    Table1
) t
WHERE   num = 1 AND name = 'ram'
ORDER BY t.id

Notice that I added the 'COLLATE Latin1_General_CS_AI' - this tells SQL Server what type of string comparison to perform, if your database collation is case-sensitive (CS) you can remove this part of query, but if your collation is case-insensitive (CI) you have to use the case sensitive version of your collation (I used Latin1_General case sensitive, accent insensitive).

Upvotes: 0

Related Questions