How to Order Entries in a Union Without Order By

How can I order entries in a UNION without ORDER BY?

SELECT col
FROM 
   (
       SELECT a col, 0 ordinal FROM A LIMIT 1
       UNION ALL
       SELECT b, 1 FROM B LIMIT 1
   ) t
ORDER BY ordinal

Sql server union but keep order

Try this :-

Select * 
from
( 
Select name,surname, 1 as filter
from  Table1
Union all
Select name,surname , 2 as filter
from Table2
)
order by filter

Keep ORDER BY after UNION

SELECT CON
FROM
(
SELECT 1 as SEQ, 0 as NR, NAME ,TEMP.CON1 as CON
FROM (
    SELECT NAME, CONCAT(NAME,'(', LEFT(OCCUPATION, 1),')') AS CON1
    FROM OCCUPATIONS
    ORDER BY NAME -- don't need 
    ) AS TEMP
UNION
SELECT 2, NR, null, TEMP2.CON2 
FROM (
    SELECT COUNT(*) AS NR, CONCAT('THERE ARE A TOTAL OF ', COUNT(OCCUPATION),' ', OCCUPATION, 's') AS CON2
    FROM OCCUPATIONS
    GROUP BY OCCUPATION
    ORDER BY NR, OCCUPATION -- don't need
) AS TEMP2 ) T 
ORDER BY SEQ, NR, NAME, CON

How to ORDER BY one query only in a UNION ALL without subquery

The ORDER BY doesn't apply to just one SELECT, it applies to the whole result set, to the whole result after all rows are UNIONed. So, you need to include columns that will be used for sorting in each SELECT of the UNION ALL.

For example, the following is wrong syntax, because ORDER BY has to be after the last SELECT of the UNION.

SELECT CASE
           WHEN p.Category = 1
           THEN 'Very Active'
           WHEN p.Category = 2
           THEN 'Active '
           ELSE 'Departed'
       END AS Matrix
     , p.name AS [Name]
FROM #People p
ORDER BY p.Category

UNION ALL

SELECT 'Best Employee' AS Matrix
     , 'Alan' AS [Name]

Your second example from the question can be written in a simpler form, without sub-query:

SELECT CASE
           WHEN p.Category = 1
           THEN 'Very Active'
           WHEN p.Category = 2
           THEN 'Active '
           ELSE 'Departed'
       END AS Matrix
     , p.name AS [Name]
     , p.Category
FROM #People p

UNION ALL

SELECT 'Best Employee' AS Matrix
     , 'Alan' AS [Name]
     , NULL AS Category  --- or '' AS Category

ORDER BY Category;

In any case, the column Category has to be added in each SELECT statement of the UNION. I don't see how you can avoid it.

How to execute UNION without sorting? (SQL)

I notice this question gets quite a lot of views so I'll first address a question you didn't ask!

Regarding the title. To achieve a "Sql Union All with “distinct”" then simply replace UNION ALL with UNION. This has the effect of removing duplicates.

For your specific question, given the clarification "The first query should have "priority", so duplicates should be removed from bottom" you can use

SELECT col1,
       col2,
       MIN(grp) AS source_group
FROM   (SELECT 1 AS grp,
               col1,
               col2
        FROM   t1
        UNION ALL
        SELECT 2 AS grp,
               col1,
               col2
        FROM   t2) AS t
GROUP  BY col1,
          col2
ORDER  BY MIN(grp),
          col1  

How can I keep the order of column values in a union select?

If you're not selecting from tables?

Then you could insert VALUES, instead of a select with unions.

insert into @QuestionOptionMapping (RateCode) values
 ('PD0116')
,('PL0090')
,('PL0091')
,('DD0026')
,('DD0025')

Or in your query, change all the UNION to UNION ALL.

The difference between a UNION and a UNION ALL is that a UNION will remove duplicate rows.
While UNION ALL just stiches the resultsets from the selects together.

And for UNION to find those duplicates, internally it first has to sort them.

But a UNION ALL doesn't care about uniqueness, so it doesn't need to sort.

A 3th option would be to simply change from 1 insert statement to multiple insert statements.
One insert per value. Thus avoiding UNION completely.

But that anti-golfcoding method is also the most wordy.

using Union and Order By without getting duplicates

Put your existing query inside a CTE (here I'm placing your sample data there instead)
and then use a ROW_NUMBER() and a further WHERE to filter the results:

with OriginalQuery as (
select 1 as Rank,  12345 as CallerID union all
select 2 ,123 union all
select 2,1233 union all
select 2,1234 union all
select 2,12345 union all
select 2,1236
), Preferred as (
    select *,ROW_NUMBER() OVER (
        PARTITION BY CallerID /* other columns too? */
        ORDER BY RANK
        ) as rn
    from OriginalQuery
)
select
    *
from
    Preferred
where
    rn = 1
order by Rank,CallerID

As indicated, you may have to add more/adjust columns to the PARTITION if CallerID isn't a key, by itself, for this data.

---

Of course, if there aren't any duplicates in your underlying data, and the reason you're getting duplicates is just because you're running two searches and combining the results, it's far simpler to do:

SELECT [CallerID] 
FROM [PHONE]
WHERE
    CallerID = '12345' OR
    ([CallerID] LIKE '12%' AND ABS(LEN([CallerID]) - LEN('12345')) < 3)
ORDER BY CASE WHEN CallerID='12345' THEN 0 ELSE 1 END, [CallerID] ASC

Where you combine both searches rather than combining their results and then use CASE to pick out the best matches in the ORDER BY.

---

Related Topics