CODEWITHSUNDEEP
mysqlsqldatabasesqlite
srb_programming
srb_programming

Reputation: 3

Why using union, result of 2 queries is mixing in SQLite?

I want to append result of 2 queries. For example:

ResultOfQuery_1

ResultOfQuery_2

My query is :

SELECT Id FName,SortString  
     FROM CTable 
     WHERE '1' = DevId AND SortString glob '[A-Z]*'      
UNION 
SELECT FName,SortString 
     FROM CTable 
     WHERE '1' = DevId AND SortString NOT glob '[A-Z]*' ORDER BY FName DESC

As an output of this query both results are mixing. I want them one after other.

Can anyone help me get the result I want?

Upvotes: 0

Views: 72

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Just use a single query:

SELECT Id FName,SortString  
FROM CTable 
WHERE 1 = DevId 
ORDER BY (SortString glob '[A-Z]*') DESC, FNAME DESC ;

If DevId is a number, then do not use single quotes around the comparison. I removed the single quotes, assuming that it is a number.

Upvotes: 0

Pasetchnik
Pasetchnik

Reputation: 318

In your case Order by is applying to whole query. If we could use brackets it wold be like

(
SELECT Id FName,SortString  
     FROM CTable 
     WHERE '1' = DevId AND SortString glob '[A-Z]*'      
UNION 
SELECT FName,SortString 
     FROM CTable 
     WHERE '1' = DevId AND SortString NOT glob '[A-Z]*' 
)
ORDER BY FName DESC

You might use some ordering field

SELECT Id, FName,SortString, 1 as OrderStr
    FROM CTable 
    WHERE '1' = DevId AND SortString glob '[A-Z]*'      
UNION 
SELECT Id, FName,SortString, 2 as OrderStr
    FROM CTable 
    WHERE '1' = DevId AND SortString NOT glob '[A-Z]*' 
ORDER BY OrderStr, FName DESC

Or maybe sub-queries

SELECT *
    FROM(
        SELECT Id, FName,SortString
        FROM CTable 
        WHERE '1' = DevId AND SortString glob '[A-Z]*'  
        ORDER BY FName DESC
        ) T1
UNION 
SELECT *
    FROM(
        SELECT Id, FName,SortString
        FROM CTable 
        WHERE '1' = DevId AND SortString NOT glob '[A-Z]*'  
        ORDER BY FName DESC
        ) T2

Upvotes: 1

Rahul
Rahul

Reputation: 77896

Then do a JOIN on sortstring column instead of UNION like

select xx.Id, xx.FName as xxFName,
xx.SortString as xxSortString,
xxx.fname, xxx.SortString
from (
SELECT Id FName,SortString  
     FROM CTable 
     WHERE '1' = DevId AND SortString glob '[A-Z]*'  ) xx
join (
SELECT FName,SortString 
     FROM CTable 
     WHERE '1' = DevId AND SortString NOT glob '[A-Z]*' ) xxx
on xx.sortstring = xxx.sortstring
 ORDER BY xx.FName DESC;

Upvotes: 0

Related Questions