|
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%'; |
|
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 or Select e1 as A, e2 as B ... Union all Select f1, f2, ... Union all ... ORDER BY B, A |
|
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. |
|
Be careful with union and order by construction. look at topic: http://sqlanywhere-forum.sap.com/questions/6778/order-by-and-union-all-compatibility |
