CODEWITHSUNDEEP
sqldb2
Zack E
Zack E

Reputation: 706

Unexpected Result SQL ORDER BY

I have a scenario that I dont know how to get the correct results due to my lack of knowledge with SQL and need assistance.

I understand what the ORDER BY command does; I just dont know how to manipulate the data returned from the query to keep the data in the order I need it to be in.

Below is the SQL String:

SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000714'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000713'
UNION
SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000715'
ORDER BY "CIF Number"

As you can see The "CIF Numbers" are out of order, which is the way they need to be and with the ORDER BY the data is ordered by the CIF#, but I need the data to actually be out of order.

Below is how the current string returns the data:

T000713 | ROGER RABBITT                          
T000714 | JESSICA RABBIT                         
T000715 | JAMES KIRK

Below is how I need the data returned:

T000714 | JESSICA RABBITT
T000713 | ROGER RABBITT
T000715 | JAMES KIRK

When I remove the ORDER BY the result returned is this:

T000714 | JAMES KIRK
T000713 | JESSICA RABBITT
T000715 | ROGER RABBITT

Upvotes: 0

Views: 190

Answers (2)

Sathvik Reddy
Sathvik Reddy

Reputation: 41

Simple!

Remove quotes from the order by clause. It should work. Your code should be like below.

`SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM 
 cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000714'
 UNION
 SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM 
 cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000713'
 UNION
 SELECT cfcif# AS "CIF Number",cfna1 AS "Customer Name" FROM 
 cncttp08.jhadat842.cfmast cfmast WHERE cfcif#='T000715'
 ORDER BY CIF Number`

Upvotes: 0

forpas
forpas

Reputation: 164089

I don't understand why you use UNION. You can do it like this:

SELECT 
  cfcif# AS "CIF Number",
  cfna1 AS "Customer Name" 
  FROM cncttp08.jhadat842.cfmast cfmast 
  WHERE cfcif# IN ('T000714', 'T000713', 'T000715')
ORDER BY
  CASE cfcif# 
    WHEN 'T000714' THEN 1
    WHEN 'T000713' THEN 2
    WHEN 'T000715' THEN 3
  END

Upvotes: 3

Related Questions