Simple organize select question

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)

Simple organize select question

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2007-04-11 : 17:25:56

HI all,

I want to order a list of domain names.
at the moment it looks like this

1sub.domain.net
1sub.domain.com
2sub.domain.net
2sub.domain.com
domain.com
domain.net
xub.domain.net
xub.domain.com

and what i what is,

domain.com
1sub.domain.com
2sub.domain.com
xub.domain.com
1sub.domain.net
2sub.domain.net
domain.net
xub.domain.net

How can i achieve this?

Another question, i got an order by date(datetime collum) but it messes up the years! any idea?

thanks a lot,

Mike

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 17:56:09

Your expected output is not consistent.

-- Prepare sample data
DECLARE	@Sample TABLE (Domain VARCHAR(200))

INSERT	@Sample
SELECT	'1sub.domain.net' UNION ALL
SELECT	'1sub.domain.com' UNION ALL
SELECT	'2sub.domain.net' UNION ALL
SELECT	'2sub.domain.com' UNION ALL
SELECT	'domain.com' UNION ALL
SELECT	'domain.net' UNION ALL
SELECT	'xub.domain.net' UNION ALL
SELECT	'xub.domain.com'

-- Show the expected output
SELECT		Domain
FROM		@Sample
ORDER BY	PARSENAME(Domain, 2),
		PARSENAME(Domain, 1),
		PARSENAME(Domain, 3)