ORDER BY Problem? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ORDER BY Problem?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-09 : 09:55:35

Hi,

I would like to use query like below;

But ORDER BY gives error!

Select top(10) * from (
SELECT table1.field1 AS 'Talebi_Yapan',
CONVERT(CHAR(10),table1.field3,104) + SUBSTRING(CONVERT(varchar,table1.field3,113),12,9) AS 'Talep_Zamani',
table1.field2 as 'EForm_Adi',
FROM table1
WHERE table1.field4 = 'abc'
UNION
SELECT table2.field1 AS 'Talebi_Yapan',
CONVERT(CHAR(10),table2.field3,104) + SUBSTRING(CONVERT(varchar,table2.field3,113),12,9) AS 'Talep_Zamani',
table2.field2 as 'EForm_Adi',
FROM table2
WHERE table2.field4 = 'abc'
) dt CONVERT(CHAR(10),table2.field3,104) + SUBSTRING(CONVERT(varchar,table2.field3,113),12,9)

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-09 : 09:57:25

Typo,

There should be ORDER BY after dt

Thanks in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-11-09 : 10:47:49

Try replacing CONVERT(CHAR(10),table2.field3,104) + SUBSTRING(CONVERT(varchar,table2.field3,113),12,9)
with 'Talep_Zamani'
Also give a length to your varchar statements, e.g. CONVERT(varchar(25),table2.Field3,113),12,9)
This code could probably be cleaned up more if we knew the data type for field3 and what you're tring to make it look like

Jim

Everyday I learn something that somebody else already knew

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-09 : 13:53:47

You also have to remove the trailing <comma> after the last column listed in each SELECT statement.
The Assuming that [field3] is a datetime column you may want to include the unformatted version of that value in each half of the union so you can ORDER by that rather than the formatted version so that it sorts in actual date order.

Be One with the Optimizer
TG

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-10 : 04:05:40

Msg 408, Level 16, State 1, Line 1
A constant expression was encountered in the ORDER BY list, position 1.

SELECT TOP(10) *
FROM(SELECT distinct Users.name AS 'Talebi_Yapan',
CONVERT(CHAR(10),Eform_IsAvans.CreationDate,104) + SUBSTRING(CONVERT(varchar,Eform_IsAvans.CreationDate,113),12,9) AS 'Talep_Zamani',
EformType.EformName as 'EForm_Adi',
Eform_IsAvans.EformID,
Eform_IsAvans.EformType,
Eform_IsAvans.CreatedBy,
EformType.EformTable
FROM Eform_IsAvans
INNER JOIN Tasks ON Eform_IsAvans.EformID = Tasks.EformID
INNER JOIN EformType ON Eform_IsAvans.EformType = EformType.EformType
INNER JOIN USers ON Eform_ISAvans.CreatedBy = Users.SamAccountName
WHERE Eform_IsAvans.IsApproved = '2' AND
Tasks.Approver = 'ckizildag'
UNION ALL
SELECT distinct Users.name AS 'Talebi_Yapan',
CONVERT(CHAR(10),Eform_Egitim.CreationDate,104) + SUBSTRING(CONVERT(varchar,Eform_Egitim.CreationDate,113),12,9) AS 'Talep_Zamani',
EformType.EformName as 'EForm_Adi',
Eform_Egitim.EformID,
Eform_Egitim.EformType,
Eform_Egitim.CreatedBy,
EformType.EformTable
FROM Eform_Egitim
INNER JOIN Tasks ON Eform_Egitim.EformID = Tasks.EformID
INNER JOIN EformType ON Eform_Egitim.EformType = EformType.EformType
INNER JOIN USers ON Eform_Egitim.CreatedBy = Users.SamAccountName
WHERE Eform_Egitim.IsApproved = '2' AND
Tasks.Approver = 'ckizildag') DT ORDER BY 'Talep_Zamani'

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 04:08:47