order by - 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

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-22 : 23:52:08

hi all,

i failed to find any topic related on what im trying to do :-
how do i order by column1 (that is varchar column) with <NULL> at the bottom without DESC ?

a
b
c
<NULL>
<NULL>

NOT
<NULL>
<NULL>
a
b
c

thanks

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-22 : 23:55:29

[code]DECLARE @TABLE TABLE
(
col varchar(10)
)

INSERT INTO @TABLE
SELECT NULL UNION ALL
SELECT 'a' UNION ALL
SELECT NULL UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'

SELECT *
FROM @TABLE
ORDER BY CASE WHEN col IS NOT NULL THEN 1 ELSE 2 END, col

/*
col
----------
a
b
c
NULL
NULL
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-22 : 23:59:11

We need to order the column by giving 5the max value for the null..

Select column from tablename
order by Isnull(column, 'zzz')

--------------------------------------------------
S.Ahamed

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-23 : 00:03:08

oh thanks... how do i do it with distinct :(

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-23 : 00:05:14

[code]DECLARE @TABLE TABLE
(
col varchar(10)
)

INSERT INTO @TABLE
SELECT NULL UNION ALL
SELECT 'a' UNION ALL
SELECT NULL UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'

SELECT col
FROM
(
SELECT DISTINCT col
FROM @TABLE
) a
ORDER BY CASE WHEN col IS NOT NULL THEN 1 ELSE 2 END, col

/*
col
----------
a
b
c
NULL
*/

SELECT col
FROM @TABLE
GROUP BY col
ORDER BY CASE WHEN col IS NOT NULL THEN 1 ELSE 2 END, col

/*
col
----------
a
b
c
NULL
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-23 : 00:16:44

got it .. thanks guys

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-23 : 01:48:02

Guys,,.,
im having another problem : Why both of this solution return me error :-
"ORDER BY items must appear in the select list if SELECT DISTINCT is specified."


SELECT	distinct consigneeid
FROM	itemdetails
GROUP BY consigneeid
ORDER BY CASE WHEN consigneeid IS NOT NULL THEN 1 ELSE 2 END, consigneeid

SELECT distinct consigneeid
FROM itemdetails
ORDER BY isnull(consigneeid,'zzz')

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-23 : 02:00:35

See Tan's second post or u have to use derived table as below

Select c1
From
(select distinct c1 from @t) as a
order by Isnull(c1,'zz')

--------------------------------------------------
S.Ahamed

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-23 : 02:11:54

oopss... actually it worked already... just i forgot to remove DIstinct when group is in use... sorry

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources