동일한 SELECT 문에서 DISTINCT 및 ORDER BY를 사용하는 방법은 무엇입니까?

동일한 SELECT 문에서 DISTINCT 및 ORDER BY를 사용하는 방법은 무엇입니까?

---

다음 문을 실행 한 후 :

SELECT  Category  FROM MonitoringJob ORDER BY CreationDate DESC

데이터베이스에서 다음 값을 얻습니다.

test3
test3
bildung
test4
test3
test2
test1

하지만 다음과 같이 중복을 제거하고 싶습니다.

bildung
test4
test3
test2
test1

DISTINCT를 사용하려고했지만 하나의 문에서 ORDER BY와 함께 작동하지 않습니다. 도와주세요.

중대한:

1. 나는 그것을 시도했다 :

   SELECT DISTINCT Category FROM MonitoringJob ORDER BY CreationDate DESC
   

   작동하지 않습니다.

2. CreationDate에 의한 주문은 매우 중요합니다.

---

문제는에서 사용 된 열 ORDER BY이 DISTINCT. 이렇게하려면 집계 함수 를 사용하여 정렬하고 a GROUP BY를 사용하여 DISTINCT작업을 수행해야합니다.

다음과 같이 시도하십시오.

SELECT DISTINCT Category, MAX(CreationDate) 
FROM MonitoringJob 
GROUP BY Category 
ORDER BY MAX(CreationDate) DESC, Category

---

확장 된 정렬 키 열

원하는 작업이 작동하지 않는 이유는 첫 번째 쿼리의 경우 다음 과 같은 SQL 의 논리적 작업 순서 때문입니다 .

• FROM MonitoringJob
• SELECT Category, CreationDate즉, 소위 확장 정렬 키 열 추가
• ORDER BY CreationDate DESC
• SELECT Category즉 , 결과에서 확장 된 정렬 키 열을 다시 제거합니다 .

따라서 SQL 표준 확장 정렬 키 열 기능 덕분에 SELECT절에 일시적으로 추가되므로 절에 없는 항목으로 정렬 하는 것이 완전히 가능 합니다.

그렇다면 왜 이것이 작동하지 DISTINCT않습니까?

DISTINCT연산 을 추가하면 SELECT과 사이에 추가됩니다 ORDER BY.

• FROM MonitoringJob
• SELECT Category, CreationDate
• DISTINCT
• ORDER BY CreationDate DESC
• SELECT Category

그러나 이제 확장 된 정렬 키 열 CreationDate 을 사용하여 DISTINCT작업 의 의미 가 변경되었으므로 결과가 더 이상 동일하지 않습니다. 이것은 우리가 원하는 것이 아니므로 SQL 표준과 모든 합리적인 데이터베이스 모두 이러한 사용을 금지합니다.

해결 방법

It can be emulated with standard syntax as follows

SELECT Category
FROM (
  SELECT Category, MAX(CreationDate) AS CreationDate
  FROM MonitoringJob
  GROUP BY Category
) t
ORDER BY CreationDate DESC

Or, just simply (in this case), as shown also by Prutswonder

SELECT Category, MAX(CreationDate) AS CreationDate
FROM MonitoringJob
GROUP BY Category
ORDER BY CreationDate DESC

I have blogged about SQL DISTINCT and ORDER BY more in detail here.

---

If the output of MAX(CreationDate) is not wanted - like in the example of the original question - the only answer is the second statement of Prashant Gupta's answer:

SELECT [Category] FROM [MonitoringJob] 
GROUP BY [Category] ORDER BY MAX([CreationDate]) DESC

Explanation: you can't use the ORDER BY clause in an inline function, so the statement in the answer of Prutswonder is not useable in this case, you can't put an outer select around it and discard the MAX(CreationDate) part.

---

Just use this code, If you want values of [Category] and [CreationDate] columns

SELECT [Category], MAX([CreationDate]) FROM [MonitoringJob] 
             GROUP BY [Category] ORDER BY MAX([CreationDate]) DESC

Or use this code, If you want only values of [Category] column.

SELECT [Category] FROM [MonitoringJob] 
GROUP BY [Category] ORDER BY MAX([CreationDate]) DESC

You'll have all the distinct records what ever you want.

---

2) Order by CreationDate is very important

The original results indicated that "test3" had multiple results...

It's very easy to start using MAX all the time to remove duplicates in Group By's... and forget or ignore what the underlying question is...

The OP presumably realised that using MAX was giving him the last "created" and using MIN would give the first "created"...

---

if object_id ('tempdb..#tempreport') is not null
begin  
drop table #tempreport
end 
create table #tempreport (
Category  nvarchar(510),
CreationDate smallint )
insert into #tempreport 
select distinct Category from MonitoringJob (nolock) 
select * from #tempreport  ORDER BY CreationDate DESC

---

By subquery, it should work:

    SELECT distinct(Category) from MonitoringJob  where Category in(select Category from MonitoringJob order by CreationDate desc);

---

Distinct will sort records in ascending order. If you want to sort in desc order use:

SELECT DISTINCT Category
FROM MonitoringJob
ORDER BY Category DESC

If you want to sort records based on CreationDate field then this field must be in the select statement:

SELECT DISTINCT Category, creationDate
FROM MonitoringJob
ORDER BY CreationDate DESC

---

You can use CTE:

WITH DistinctMonitoringJob AS (
    SELECT DISTINCT Category Distinct_Category FROM MonitoringJob 
)

SELECT Distinct_Category 
FROM DistinctMonitoringJob 
ORDER BY Distinct_Category DESC

---

Try next, but it's not useful for huge data...

SELECT DISTINCT Cat FROM (
  SELECT Category as Cat FROM MonitoringJob ORDER BY CreationDate DESC
);

---

It can be done using inner query Like this

$query = "SELECT * 
            FROM (SELECT Category  
                FROM currency_rates                 
                ORDER BY id DESC) as rows               
            GROUP BY currency";

---

SELECT DISTINCT Category FROM MonitoringJob ORDER BY Category ASC

참고URL : https://stackoverflow.com/questions/5391564/how-to-use-distinct-and-order-by-in-same-select-statement