Union ORDER BY with expression

Question

Post reply

Union ORDER BY with expression

Sam Jaynes

Mr or Mrs. 500

Points: 506
More actions
September 27, 2006 at 12:12 pm

#113857

I have a union query that has two aliases vDay based upon :
SELECT convert(varchar, datepart(dd, s.dtDate)) 'vDay' .... FROM Schedule as S
UNION
SELECT convert(varchar, datepart(dd, h.dtDate)) 'vDay' .... FROM Holidays as H
However, I am trying to Order by vDay (but as an INT)... I tried ORDER BY CAST(vDay as INT) with no avail. The ASP frontend needs vDay to be a varchar, but I need to order the recordset.
"Life without progression is entropy"
Sam Jaynes

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

fhanlon SSChampion Points: 13131 More actions · Answer 1

Did you try

SELECT t.vDay FROM (SELECT CONVERT(VARCHAR, DATEPART(dd, s.dtDate)) 'vDay' .... FROM Schedule AS S

UNION

SELECT CONVERT(VARCHAR, DATEPART(dd, h.dtDate)) 'vDay' .... FROM Holidays AS H

ORDER BY vday

Francis

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

Try This:

Select tableName.Col1,tableName.Col2,tableName.Col3,... from (

SELECT convert(varchar, datepart(dd, s.dtDate)) 'vDay' ....,datepart(dd, s.dtDate) as Dy FROM Schedule as S

UNION

SELECT convert(varchar, datepart(dd, h.dtDate)) 'vDay' ....,datepart(dd, H.dtDate) as Dy FROM Holidays as H) tableName

Order by Dy

Thanks

Sreejith

Sam Jaynes Mr or Mrs. 500 Points: 506 More actions · Answer 3

Since the column is varchar, it would order things like 1,11,12,13,14,2,21,22,25,3,30,31

"Life without progression is entropy"
Sam Jaynes

Sam Jaynes Mr or Mrs. 500 Points: 506 More actions · Answer 4

I will try out the subquery T-SQL

"Life without progression is entropy"
Sam Jaynes

fhanlon SSChampion Points: 13131 More actions · Answer 5

ok then how about

SELECT t.vDay ... FROM (SELECT CONVERT(VARCHAR, DATEPART(dd, s.dtDate)) 'vDay' ,CONVERT(int, DATEPART(dd, s.dtDate)) 'vDayorderby' .... FROM Schedule AS S

UNION

SELECT CONVERT(VARCHAR, DATEPART(dd, h.dtDate)) 'vDay' ,CONVERT(int, DATEPART(dd, s.dtDate)) 'vDayorderby' .... FROM Holidays AS H

ORDER BY vDayorderby

Francis

Sergiy SSC Guru Points: 110192 More actions · Answer 6

DATEPART returns int (see BOL)

So, there is no point in using

CONVERT(int, DATEPART(dd, s.dtDate))

_____________
Code for TallyGenerator

Steve Fine Ten Centuries Points: 1134 More actions · Answer 7

You might try something like this from your original Query in lieu of the subquery to sort by:

SELECT Case when DATEPART(dd,dtDate) < 10 then '0' + CONVERT(VARCHAR(1),DATEPART(dd, dtDate)) else Convert(Varchar(2),DATEPART(dd, dtDate))end as 'vDay' FROM Schedule

which will give you the Varchar(2) of the Day to sort by.