选择不同的记录order by子句

时间：2021-05-24 22:45:14

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

现在我需要选择所有不同的名称，并具有最小ID号。结果应该是

1, Ram
6, ram
7, RAM

3 个解决方案

#1

0  

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).

请注意，我添加了'COLLATE Latin1_General_CS_AI' - 这告诉SQL Server要执行什么类型的字符串比较，如果您的数据库排序规则区分大小写（CS），您可以删除此部分查询，但是如果您的排序规则不区分大小写（ CI）你必须使用整理的大小写敏感版本（我使用Latin1_General区分大小写，不区分重音）。

#2

5  

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

#3

0  

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

#1

0  

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).

请注意，我添加了'COLLATE Latin1_General_CS_AI' - 这告诉SQL Server要执行什么类型的字符串比较，如果您的数据库排序规则区分大小写（CS），您可以删除此部分查询，但是如果您的排序规则不区分大小写（ CI）你必须使用整理的大小写敏感版本（我使用Latin1_General区分大小写，不区分重音）。

#2

5  

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

#3

0  

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

相关文章