I have this table

Here I want to arranged table in ascending order order by firstname and those who are from UK by customer id. I tried doing this

select *
from tblCustomers
order by FirstName, LastName, age asc
where not country = 'uk'

but we can't do it as where clause should be used after table name and when we do

select *
from tblCustomers
where not country = 'uk'
order by FirstName, LastName, age asc

It exclude UK which is not my desired solution. How can I arranged in ascending order, order by firstname and firstname from UK from customer Id?

CodePudding user response：

Build the order by clause up using case expressions e.g.

declare @tblCustomers table (customer_id int, first_name varchar(12), last_name varchar(12), age int, country varchar(3));

insert into @tblCustomers (customer_id, first_name, last_name, age, country)
values
(1, 'John', 'Doe', 31, 'USA'),
(2, 'Robert', 'Luna', 22, 'USA'),
(3, 'David', 'Robinson', 22, 'UK'),
(4, 'John', 'Reinhardt', 25, 'UK'),
(5, 'Betty', 'Doe', 28, 'UAE');

select *
from @tblCustomers
order by
    case when country = 'UK' then 1 else 0 end asc -- non-UK first
    , case when country = 'UK' then customer_id else null end asc -- UK ordered by id
    , first_name, last_name, age asc; -- Non-UK ordered as specified

Returns:

Notes:

• please don't use images as we can't copy and paste the data
• ideally provide the DDL DML (as I have shown) as this makes it much easier to answer
• always ensure your question is consistent, with the same table and columns names throughout
• always provide your desired results
• don't store age in a database, store date of birth and calculate age.