UNION 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

UNION order by problem??

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2011-01-05 : 05:07:52

Hi,

I have a query like below. The result of the query is not Descending order! Any help please?

Thanks in advance.

Best Regards.

SELECT * FROM (SELECT Users.name AS 'Talebi_Yapan',
CONVERT(CHAR(10),Masraf.CreationDate,104) + SUBSTRING(CONVERT(varchar,Masraf.CreationDate,113),12,9) AS 'Talep_Zamani',
EformType.EformName as 'Adi',
Masraf.EformID,
Masraf.EformType,
Masraf.CreatedBy,
EformType.EformTable
FROM Masraf
INNER JOIN EformType ON Masraf.EformType = EformType.EformType
INNER JOIN USers ON Masraf.CreatedBy = Users.SamAccountName
WHERE Masraf.IsApproved = '1' AND
Masraf.CreatedBy = ''abc''
UNION ALL
SELECT Users.name AS 'Talebi_Yapan',
CONVERT(CHAR(10),Terminal.CreationDate,104) + SUBSTRING(CONVERT(varchar,Terminal.CreationDate,113),12,9) AS 'Talep_Zamani',
EformType.EformName as 'Adi',
Terminal.EformID,
Terminal.EformType,
Terminal.CreatedBy,
EformType.EformTable
FROM Terminal
INNER JOIN EformType ON Terminal.EformType = EformType.EformType
INNER JOIN USers ON Terminal.CreatedBy = Users.SamAccountName
WHERE Terminal.IsApproved = '1' AND
Terminal.CreatedBy = ''abc''
UNION ALL
SELECT Users.name AS 'Talebi_Yapan',
CONVERT(CHAR(10),IseBaslama.CreationDate,104) + SUBSTRING(CONVERT(varchar,IseBaslama.CreationDate,113),12,9) AS 'Talep_Zamani',
EformType.EformName as 'Adi',
IseBaslama.EformID,
IseBaslama.EformType,
IseBaslama.CreatedBy,
EformType.EformTable
FROM IseBaslama
INNER JOIN EformType ON IseBaslama.EformType = EformType.EformType
INNER JOIN Users ON IseBaslama.CreatedBy = Users.SamAccountName
WHERE IseBaslama.IsApproved = '1' AND
IseBaslama.CreatedBy = ''abc'' ) DT ORDER BY Talep_Zamani DESC

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 05:22:30

The column value is a varchar. So it is ordered on the characters than the datetime. Use actual dates and do the formation at the front end application

Madhivanan

Failing to plan is Planning to fail

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2011-01-05 : 05:43:50

So if i change query like below, is there a way to format the 'Talep_Zamani'?

SELECT * FROM (SELECT Users.name AS 'Talebi_Yapan',
Masraf.CreationDate AS 'Talep_Zamani',
EformType.EformName as 'Adi',
Masraf.EformID,
Masraf.EformType,
Masraf.CreatedBy,
EformType.EformTable
FROM Masraf
INNER JOIN EformType ON Masraf.EformType = EformType.EformType
INNER JOIN USers ON Masraf.CreatedBy = Users.SamAccountName
WHERE Masraf.IsApproved = '1' AND
Masraf.CreatedBy = 'abc'
UNION ALL
SELECT Users.name AS 'Talebi_Yapan',
Terminal.CreationDate AS 'Talep_Zamani',
EformType.EformName as 'Adi',
Terminal.EformID,
Terminal.EformType,
Terminal.CreatedBy,
EformType.EformTable
FROM Terminal
INNER JOIN EformType ON Terminal.EformType = EformType.EformType
INNER JOIN USers ON Terminal.CreatedBy = Users.SamAccountName
WHERE Terminal.IsApproved = '1' AND
Terminal.CreatedBy = 'abc'
UNION ALL
SELECT Users.name AS 'Talebi_Yapan',
IseBaslama.CreationDate AS 'Talep_Zamani',
EformType.EformName as 'Adi',
IseBaslama.EformID,
IseBaslama.EformType,
IseBaslama.CreatedBy,
EformType.EformTable
FROM IseBaslama
INNER JOIN EformType ON IseBaslama.EformType = EformType.EformType
INNER JOIN Users ON IseBaslama.CreatedBy = Users.SamAccountName
WHERE IseBaslama.IsApproved = '1' AND
IseBaslama.CreatedBy = 'abc' ) DT ORDER BY Talep_Zamani DESC

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 06:02:41

One method is

set dateformat dmy

SELECT * FROM (SELECT Users.name AS 'Talebi_Yapan',
CONVERT(CHAR(10),Masraf.CreationDate,104) + SUBSTRING(CONVERT(varchar,Masraf.CreationDate,113),12,9) AS 'Talep_Zamani',
EformType.EformName as 'Adi',
Masraf.EformID,
Masraf.EformType,
Masraf.CreatedBy,
EformType.EformTable
FROM Masraf
INNER JOIN EformType ON Masraf.EformType = EformType.EformType
INNER JOIN USers ON Masraf.CreatedBy = Users.SamAccountName
WHERE Masraf.IsApproved = '1' AND
Masraf.CreatedBy = ''abc''
UNION ALL
SELECT Users.name AS 'Talebi_Yapan',
CONVERT(CHAR(10),Terminal.CreationDate,104) + SUBSTRING(CONVERT(varchar,Terminal.CreationDate,113),12,9) AS 'Talep_Zamani',
EformType.EformName as 'Adi',
Terminal.EformID,
Terminal.EformType,
Terminal.CreatedBy,
EformType.EformTable
FROM Terminal
INNER JOIN EformType ON Terminal.EformType = EformType.EformType
INNER JOIN USers ON Terminal.CreatedBy = Users.SamAccountName
WHERE Terminal.IsApproved = '1' AND
Terminal.CreatedBy = ''abc''
UNION ALL
SELECT Users.name AS 'Talebi_Yapan',
CONVERT(CHAR(10),IseBaslama.CreationDate,104) + SUBSTRING(CONVERT(varchar,IseBaslama.CreationDate,113),12,9) AS 'Talep_Zamani',
EformType.EformName as 'Adi',
IseBaslama.EformID,
IseBaslama.EformType,
IseBaslama.CreatedBy,
EformType.EformTable
FROM IseBaslama
INNER JOIN EformType ON IseBaslama.EformType = EformType.EformType
INNER JOIN Users ON IseBaslama.CreatedBy = Users.SamAccountName
WHERE IseBaslama.IsApproved = '1' AND
IseBaslama.CreatedBy = ''abc'' ) DT ORDER BY cast(Talep_Zamani as datetime) DESC

Madhivanan

Failing to plan is Planning to fail

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2011-01-05 : 06:14:54

Thanks so much

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 08:03:22