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?
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