Why the grouped query is sorting A to Z by default?

Question

I've created a query to get the values from the table's columns which are not null through this query:

Select * from(
SELECT       OEID, Chest_Pain as Head, Chest_PainComment as Detail
FROM            tblComplaints
union

SELECT       OEID, SOB as Head, SOBComment as Detail
FROM            tblComplaints
union

SELECT        OEID, PND as Head, Cyanosis as Detail FROM tblComplaints
union

SELECT        OEID, Odema_Feet as Head, Vertigo + as Detail  From tblComplaints
union

SELECT       OEID, DM as Head, DMComment as Detail
FROM            tblComplaints
union

SELECT       OEID, RS as Head, RSComment as Detail
FROM            tblComplaints

) as t
where (Head is not null and ltrim(rtrim(Head)) <> '')
and OEID = 6012

And the data is coming up fine but the problem is that this query automatically doing A to Z sort in the output result. What I need to do is to get the result by the sort of how I entered each line.

For-Example: Currently I am getting the output of this query as below:

Head                            Detail
Chest_Pain                      Chest_PainComment
DM                              DmComment
Odema_Feet                      Vertigo
PND                             Cyanosis
RS                              RSComment

And I want it to be like this:

Head                            Detail
Chest_Pain                      Chest_PainComment
RS                              RSComment
PND                             Cyanosis
DM                              DMComment

The bottom line is there should not be A to Z sort, that is happening in my query. I don't know why this A to Z sorting is happening in the query while I've not sorted it anywhere.

I will appreciate the help.

Gordon Linoff · Accepted Answer · 2019-08-11T12:06:33.843

SQL tables and results sets represent unordered sets (well, technically multi-sets). There is no ordering unless you specify an order by clause. So add one in:

select OEID, Head, Detail
from ((select OEID, Chest_Pain as Head, Chest_PainComment as Detail, 1 as ord
       from tblComplaints
      ) union all
      (select OEID, SOB as Head, SOBComment as Detail, 2
       from tblComplaints
      ) union all
      (select OEID, PND as Head, Cyanosis as Detail, 3
       from tblComplaints
      ) union all
      (select OEID, Odema_Feet as Head, Vertigo as Detail, 4
       from tblComplaints
      ) union all
      (select OEID, DM as Head, DMComment as Detail, 5
       from tblComplaints
      ) union all
      (select OEID, RS as Head, RSComment as Detail, 6
       from tblComplaints
      )
     ) as t
where Head is not null and 
      ltrim(rtrim(Head)) <> '' and
      OEID = 6012
order by ord;

The condition head is not null is redundant. The <> takes care of this.

You can simplify the query to:

select v.*
from tblComplaints t cross apply
     (values (Chest_Pain, Chest_PainComment, 1),
             (SOB, SOBComment, 2),
             . . .  -- continue with the other values
     ) v(Head, Detail, ord)
where ltrim(rtrim(Head)) <> '' and
      OEID = 6012
order by ord;

If your data is not trivially small, you should find that this has better performance as well.

Finally, to answer your question, union is removing duplicates. In this case, it would appear to be doing so by sorting the data. You cannot depend on this sort -- for instance, there are other ways to remove duplicates.

Similarly, using union all does not sort the data, but that does not mean that the result set will be in your desired order unless you have an explicit order by.

awesome man. This solved my problem. I was trying to solve this from the last 3 hours. and you just solved it in a minuted. Thank you very much. — Muhammad Noman, Aug 11 '19 at 12:19

Why the grouped query is sorting A to Z by default?

1 Answers1