I want List of party names with 1st option as 'All' from database. but i won't insert 'All' to Database, needs only retrieve time. so, I wrote this query.
Select 0 PartyId, 'All' Name
Union
select PartyId, Name
from PartyMst
This is my Result
0 All
1 SHIV ELECTRONICS
2 AAKASH & CO.
3 SHAH & CO.
when I use order by Name
it displays below result.
2 AAKASH & CO.
0 All
3 SHAH & CO.
1 SHIV ELECTRONICS
But, I want 1st Option as 'All' and then list of Parties in Sorted order. How can I do this?
It is not possible to use two different ORDER BY in the UNION statement. UNION returns single resultsetand as per the Logical Query Processing Phases.
Union is a type of operator in MySQL. We can use ORDER BY with this to filter records. Use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set. Let us see an example.
The UNION ALL operator can use the ORDER BY clause to order the results of the query in SQL Server (Transact-SQL).
You need to use a sub-query with CASE
in ORDER BY
clause like this:
SELECT * FROM
(
Select 0 PartyId, 'All' Name
Union
select PartyId, Name
from PartyMst
) tbl
ORDER BY CASE WHEN PartyId = 0 THEN 0 ELSE 1 END
,Name
Output:
PARTYID | NAME |
---|---|
0 | All |
2 | AAKASH & CO. |
3 | SHAH & CO. |
1 | SHIV ELECTRONICS |
See this SQLFiddle
Since you are anyway hardcoding 0, All just add a space before the All
Select 0 PartyId, ' All' Name
Union
select PartyId, Name
from PartyMst
ORDER BY Name
SQL FIDDLE
Raj
You can use 'order by' with the top instrucction in a union in this way:
Select 0 PartyId, 'All' Name
Union
select * from (
select top (select count(*) from PartyMst) PartyId, Name
from PartyMst
order by Name
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With