Return rows in ascending order

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

SQL Server 2000 Forums

Transact-SQL (2000)

Return rows in ascending order

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2008-06-25 : 05:48:25

I have the following select statement which I want to select a list of employments from a table but want them in ascending order. However the code only extracts them in ascending order if I remove the formatting of the date. But I need it in this format i.e 20/06/2008 etc. Can anyone help?


SELECT DISTINCT   
		emp.PNumber AS PNumber, 
		ee.NhNumber As EmpNhNo,
		CONVERT(VARCHAR(10),emp.StartDate,103) As StartDate
	FROM Employment emp 
	INNER JOIN Employee ee ON emp.EmployeeId = ee.[Id] 

	WHERE     (ee.NhNumber = '1')  
		 
	ORDER BY CONVERT(VARCHAR(10),emp.StartDate,103) ASC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 05:53:05

[code]
DECLARE @Sample TABLE (dt DATETIME)

INSERT @Sample
SELECT '20080422' UNION ALL
SELECT '20080517'

select c
from (
SELECT DISTINCT
CONVERT(VARCHAR(20), dt, 103) AS c,
dt
FROM @Sample
) as d
ORDER BY dt[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 05:53:24

may be this:-

SELECT DISTINCT   
		emp.PNumber AS PNumber, 
		ee.NhNumber As EmpNhNo,
		CONVERT(VARCHAR(10),emp.StartDate,103) As DispStartDate
	FROM Employment emp 
	INNER JOIN Employee ee ON emp.EmployeeId = ee.[Id] 

	WHERE     (ee.NhNumber = '1')  
		 
	ORDER BY emp.StartDate ASC

Looper
Yak Posting Veteran

68 Posts

Posted - 2008-06-25 : 05:57:08

when i try to run that i get the error
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 06:04:08

quote:
Originally posted by Looper

when i try to run that i get the error
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

SELECT PNumber,EmpNhNo,DispStartDate
FROM
(
SELECT DISTINCT   
		emp.PNumber AS PNumber, 
		ee.NhNumber As EmpNhNo,
                emp.StartDate,
		CONVERT(VARCHAR(10),emp.StartDate,103) As DispStartDate
	FROM Employment emp 
	INNER JOIN Employee ee ON emp.EmployeeId = ee.[Id] 

	WHERE     (ee.NhNumber = '1')  )tmp
		 
	ORDER BY StartDate ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources