SQL 쿼리-UNION에서 Order By 사용

SQL 쿼리-UNION에서 Order By 사용

---

두 테이블에서 데이터를 가져올 때 통합 쿼리를 프로그래밍 방식으로 어떻게 정렬 할 수 있습니까? 예를 들면

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY table2.field1

예외가 발생합니다.

참고 : 이것은 MS Access Jet 데이터베이스 엔진에서 시도되고 있습니다.

---

때때로 당신은있을 필요가 ORDER BY결합해야하는 각 섹션에 UNION.

이 경우

SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2

---

SELECT field1 FROM table1
UNION
SELECT field1 FROM table2
ORDER BY field1

---

나는 이것이 설명하는 데 좋은 일이라고 생각합니다.

다음은 ORDER BY 절을 사용하는 UNION 쿼리입니다.

select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
UNION
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

두 "select"문간에 열 이름이 다르기 때문에 결과 집합에서의 위치에 따라 ORDER BY 절의 열을 참조하는 것이 더 유리합니다.

이 예에서는 "ORDER BY 2"로 표시된대로 오름차순으로 supplier_name/ 기준 company_name으로 결과를 정렬했습니다 .

supplier_name/의 company_name필드는 결과 세트 # 2의 위치에있다.

여기에서 가져옴 : http://www.techonthenet.com/sql/union.php

---

구체적인 예를 사용하여 :

SELECT name FROM Folders ORDER BY name
UNION
SELECT name FROM Files ORDER BY name

파일 :

name
=============================
RTS.exe
thiny1.etl
thing2.elt
f.txt
tcpdump_trial_license (1).zip

폴더 :

name
============================
Contacts
Desktop
Downloads
Links
Favorites
My Documents

원하는 출력 : (첫 번째 선택의 결과, 즉 폴더 먼저)

Contacts
Desktop
Downloads
Favorites
Links
My Documents
f.txt
RTMS.exe
tcpdump_trial_license (1).zip
thiny1.etl
thing2.elt

원하는 결과를 얻기위한 SQL :

SELECT name 
FROM (
    SELECT 1 AS rank, name FROM Folders
    UNION 
    SELECT 2 AS rank, name FROM Files) dt
ORDER BY rank, name

---

다음은 Northwind 2007의 예입니다.

SELECT [Product ID], [Order Date], [Company Name], [Transaction], [Quantity]
FROM [Product Orders]
UNION SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity]
FROM [Product Purchases]
ORDER BY [Order Date] DESC;

ORDER BY 절은 모든 결합을 완료 한 후 마지막 문이어야합니다. 여러 세트를 합친 다음 마지막 세트 뒤에 ORDER BY 절을 넣을 수 있습니다.

---

(SELECT table1.field1 FROM table1 
UNION
SELECT table2.field1 FROM table2) ORDER BY field1 

작업? 생각 세트를 기억하십시오. 유니온을 사용하여 원하는 세트를 얻은 다음 작업을 수행하십시오.

---

SELECT table1Column1 as col1,table1Column2 as col2
    FROM table1
UNION
(    SELECT table2Column1 as col1, table1Column2 as col2
         FROM table2
)
ORDER BY col1 ASC

---

SELECT field1
FROM ( SELECT field1 FROM table1
       UNION
       SELECT field1 FROM table2
     ) AS TBL
ORDER BY TBL.field1

(ALIAS 사용)

---

이것은 내가 본 것 중 가장 어리석은 일이지만 효과가 있으며 결과에 대해 논쟁 할 수 없습니다.

SELECT *
FROM (
    SELECT table1.field1 FROM table1 ORDER BY table1.field1
    UNION
    SELECT table2.field1 FROM table2 ORDER BY table2.field1
) derivedTable

The interior of the derived table will not execute on its own, but as a derived table works perfectly fine. I've tried this on SS 2000, SS 2005, SS 2008 R2, and all three work.

---

This is how it is done

select * from 
    (select top 100 percent pointx, pointy from point
     where pointtype = 1
     order by pointy) A
union all
select * from 
    (select top 100 percent pointx, pointy from point
     where pointtype = 2
     order by pointy desc) B

---

Browsing this comment section I came accross two different patterns answering the question. Sadly for SQL 2012, the second pattern doesn't work, so here's my "work around"

---

Order By on a Common Column

This is the easiest case you can encounter. Like many user pointed out, all you really need to do is add an Order By at the end of the query

SELECT a FROM table1
UNION
SELECT a FROM table2
ORDER BY field1

or

SELECT a FROM table1 ORDER BY field1
UNION
SELECT a FROM table2 ORDER BY field1

---

Order By on Different Columns

Here's where it actually gets tricky. Using SQL 2012, I tried the top post and it doesn't work.

SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2

Following the recommandation in the comment I tried this

SELECT * FROM 
(
  SELECT TOP 100 PERCENT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT TOP 100 PERCENT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2

This code did compile but the DUMMY_ALIAS1 and DUMMY_ALIAS2 override the Order By established in the Select statement which makes this unusable.

The only solution that I could think of, that worked for me was not using a union and instead making the queries run individually and then dealing with them. So basically, not using a Union when you want to Order By

---

By using order separately each subset gets order, but not the whole set, which is what you would want uniting two tables.

You should use something like this to have one ordered set:

SELECT TOP (100) PERCENT field1, field2, field3, field4, field5 FROM 
(SELECT table1.field1, table1.field2, table1.field3, table1.field4, table1.field5 FROM table1
UNION ALL 
SELECT table2.field1, table2.field2, table2.field3, table2.field4, table2.field5 FROM  table2) 
AS unitedTables ORDER BY field5 DESC

---

The second table cannot include the table name in the ORDER BY clause.

So...

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY field1

Does not throw an exception

---

If necessary to keep the inner sorting:

SELECT 1 as type, field1 FROM table1 
UNION 
SELECT 2 as type, field1 FROM table2 
ORDER BY type, field1

---

(SELECT FIELD1 AS NEWFIELD FROM TABLE1 ORDER BY FIELD1)
UNION
(SELECT FIELD2 FROM TABLE2 ORDER BY FIELD2)
UNION
(SELECT FIELD3 FROM TABLE3 ORDER BY FIELD3) ORDER BY NEWFIELD

Try this. It worked for me.

---

For Sql Server 2014/2012/Others(Not Checked) :

SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) 
as DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) 
as DUMMY_ALIAS2

참고URL : https://stackoverflow.com/questions/213851/sql-query-using-order-by-in-union