CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Pradeep Nayak
Pradeep Nayak

Reputation: 675

Weird SQL query

I am working on a project and I have a weird requirement.

I have a table A with fields (TechID(Primary Key), Orders (INTEGER NUMBER),Name (Varchar)).

The values of Orders can be 0, 1, 2, 3, ....... My task is to display the contents of the table ordered by Orders field. rows where the Orders field has a value of 1 are displayed first, followed sequentially by the rest. rows with a value of 0 must be displayed at the end. If all the Orders field's values are 0 I need to order the table by the TechID field.

Any ideas about achieving this ? Can this be achieved with a SQL query or should I write a T-Sql script for this ?

Upvotes: 0

Views: 785

Answers (4)

Predo
Predo

Reputation: 76

Dont know if it will work on databases other then oracle's

SELECT * FROM T ORDER BY orders nulls last

Upvotes: 0

Nicholas Carey
Nicholas Carey

Reputation: 74277

That's an easy one:

select *
from A
order by case Orders     -- 1. Orders values
           when 0 then 2 --    of zero collate after
           else        1 --    non-zero Orders values
         end ,           --
         Orders ,        -- 2. Then collate by Orders value proper
         TechID          -- 3. Then by TechID

Upvotes: 9

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

Didn't actually test it, but this general idea should work:

SELECT * FROM A
ORDER BY
    CASE Orders WHEN 0 THEN 2147483647 ELSE Orders END,
    TechID

NOTE: This assumes Orders is an int. If its a bigint use 9,223,372,036,854,775,807 instead.

Upvotes: 3

Keith
Keith

Reputation: 21244

Use a case statement in the ORDER BY clause. The secondary sort by TechID will take care of your requirement to sort by TechID when all Orders values are 0.

SELECT *
FROM A
ORDER BY
   Case
      When (Orders = 0) Then 9999
      Else   Orders
   End,
   TechID

Upvotes: 1

Related Questions