CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Joshua Andrew Phillips
Joshua Andrew Phillips

Reputation: 13

Changing position of a row in sql

enter image description here In the above t-sql table I would like very much for the Total row to appear at the bottom. I have been wracking my head against this and in all of my other Queries simply using ORDER BY Status works as Total is alphabetically much farther down the list than most of our row values.

This is not the case here and I just can't figure out how to change it

I'm pretty new to sql and I'be been having a lot of difficulty even determining how to phrase a google search. So far I've just gotten results pertaining to Order By

Upvotes: 0

Views: 3260

Answers (4)

Nicholas Carey
Nicholas Carey

Reputation: 74177

The results of a select query, unless an order is explicitly specified via an 'order by' clause, can be returned in any order. Moreover, the order in which they are returned is not even deterministic. Running the exact same query 3 times in succession might return the exact same result set in 3 different orderings.

So if you want a particular order to your table, you need to order it. An order by clause like

select *
from myTable t
where ...
order by case Status when 'Total' then 1 else 0 end ,
         Status

would do you. The 'Total' row will float to the bottom, the other rows will be ordered in collating sequence. You can also order things arbitrarily with this technique:

select *
from myTable t
where ...
order by case Status
           when 'Deceased'  then 1
           when 'Total'     then 2
           when 'Active'    then 3
           when 'Withdrawn' then 4
           else                  5
         end

will list the row(s) with a status of 'Deceased' first, followed by the row(s) with a status of 'Total', then 'Active' and 'Withdrawn', and finally anything that didn't match up to an item in the list.

Upvotes: 8

Andomar
Andomar

Reputation: 238048

In SQL Server (and most other databases), you can use case to sort certain statūs above others:

order by
        case Status
        when 'Total' then 2
        else 1
        end
,       Status

In MS Access, you can use iif:

order by
        iif(Status = 'Total', 2, 1)
,       Status

Upvotes: 1

Declan_K
Declan_K

Reputation: 6826

ORDER BY CASE WHEN STATUS = 'Total' THEN 'zzz' ELSE STATUS END

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use conditional expressions in order by:

order by (case when status = 'Total' then 1 else 0 end),
         status

Upvotes: 0

Related Questions