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%';

asked 09 Nov '12, 15:11

DSAJones's gravatar image

DSAJones
31113
accept rate: 0%

retagged 18 Nov '12, 10:27

Nica%20_SAP's gravatar image

Nica _SAP
866722


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
permanent link

answered 17 Nov '12, 13:08

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

edited 17 Nov '12, 14:35

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266

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.

permanent link

answered 09 Nov '12, 15:21

DSAJones's gravatar image

DSAJones
31113
accept rate: 0%

Be careful with union and order by construction.

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

permanent link

answered 18 Nov '12, 07:37

AlexeyK77's gravatar image

AlexeyK77
70761224
accept rate: 8%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×13
×10
×10

question asked: 09 Nov '12, 15:11

question was seen: 900 times

last updated: 18 Nov '12, 10:27