Question Re: Syntax problem with Order By in Technology Q&A

Syntax problem with Order By

Former Member — Fri, 09 Nov 2012 15:11:42 GMT

Hi,

I have no Sybase SQL experience at all but occasionally use MS SQL. I have a query that works fine but I need to ensure the output is ordered correctly.

The query is to select a list of customers from a database that have their birthday in a given month. Their actual date of birth is not relevant and I just want to order the list by the day of the month. The day of the month is being returned correctly by the string(datepart(day,[Date_Of_Birth])) element but no matter where I put an Order By statement it gets rejected. Any help gratefully received by this newbie 🙂

SELECT 
  'Equip_ID', 'Title', 'Last_Name', 'Auth_name','Date_of_birth' 
UNION 
SELECT 
  string (Equip_id),Title, Last_Name, Auth_Name, string(datepart(day,[Date_Of_Birth])) 
FROM 
  resident 
INNER JOIN 
  EPEC_LOCATION 
ON 
  LOCATION_DEF = LOCATION_REF 
JOIN 
  authority 
WHERE 
  datepart (month, [Date_Of_Birth]) ='12' 
AND 
  auth_name like 'Wtc%';

Re: Syntax problem with Order By

Former Member — Fri, 09 Nov 2012 15:21:55 GMT

Sorted!! I just looked at it and realised that the UNION was completely unnecessary. I've removed that and added the statement order by datepart(day, [Date_Of_Birth]) and it's working fine.

Re: Syntax problem with Order By

Former Member — Sat, 17 Nov 2012 13:08:30 GMT

The UNION statement supports the ORDER BY clause using the positions of the columns or the names of the select list items from the first select query block. E.g., these statements will return the result set ordered by second and first columns.

Select e1 as A, e2 as B, ...
 Union all
Select f1, f2, ...
 Union all
...
ORDER BY 2, 1

Select e1 as A, e2 as B
...
 Union all
Select f1, f2, ...
 Union all
...
ORDER BY B, A

Re: Syntax problem with Order By

Former Member — Sun, 18 Nov 2012 07:37:59 GMT

Be careful with union and order by construction.

look at topic: http://sqlanywhere-forum.sap.com/questions/6778/order-by-and-union-all-compatibility