如何在SQL中使用Subinct with Order by SubString？

How can I use Distinct with Order by SubString in SQL?

如何在SQL中使用Subinct with Order by SubString?

My query is

我的疑问是

  SELECT 
 Distinct [Units Period]   
 FROM Table_Name
ORDER BY RIGHT([Units Period], 4) DESC , SUBSTRING([Units Period], 5, 2) DESC

But it didnt work.

但它没有用。

2 个解决方案

#1

The error is: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

错误是:如果指定了SELECT DISTINCT,则ORDER BY项必须出现在选择列表中。

so rewrite query with GROUP BY

所以使用GROUP BY重写查询

SELECT 
  [Units Period]   
FROM 
  Table_Name
GROUP BY
  [Units Period]   
ORDER BY 
  RIGHT([Units Period], 4) DESC , 
  SUBSTRING([Units Period], 5, 2) DESC

or add all the columns after distinct:

或者在distinct之后添加所有列:

SELECT 
  distinct
  [Units Period],
  RIGHT([Units Period], 4), 
  SUBSTRING([Units Period], 5, 2)
FROM 
  Table_Name
ORDER BY 
  RIGHT([Units Period], 4) DESC , 
  SUBSTRING([Units Period], 5, 2) DESC

#2

You can re-write the query as:

您可以将查询重写为:

 Select [Units Period] 
 From (
       SELECT 
       Distinct [Units Period]   
       FROM Table_Name) T
ORDER BY RIGHT([Units Period], 4) DESC , SUBSTRING([Units Period], 5, 2) DESC

#1