Order By trouble

Question

Post reply

Order By trouble

davdam8

Hall of Fame

Points: 3620
More actions
July 22, 2013 at 9:46 am

#280698

Hi geniuses,
I need to order some data and when Value = Null -> 'Not Defined'.
Next I need the Value 'Not Defined' to be odered in the first row.
INSERT INTO #mytable
(ID, Value)
SELECT '4','First' UNION ALL
SELECT '37','Second'UNION ALL
SELECT '44','Second' UNION ALL
SELECT '54','Third' UNION ALL
SELECT '55','' UNION ALL
SELECT '81','Fifth' UNION ALL
SELECT '86','' UNION ALL
SELECT '96',''
I tried something like:
Select ISNULL(Value, 'Not Defined') as Value
from #mytable
UNION
Select Value AS Value
FROM #mytable
Order by Value
Thanks in advance

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63487 More actions · Answer 1

Select ISNULL(Value, 'Not Defined') as Value

from #mytable

UNION

Select Value AS Value

FROM #mytable

Order by Value

How about

SELECT ISNULL(Value, 'Not Defined') AS Value, 0 As SortOrder'

FROM #mytable

UNION

SELECT Value, 1

FROM #mytable

ORDER BY Value, SortOrder

davdam8 Hall of Fame Points: 3620 More actions · Answer 2

davdam8

Hall of Fame

Points: 3620

July 22, 2013 at 9:56 am

#1634542

Not working buddy

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

davdam8 (7/22/2013)
Not working buddy

In your sample data you don't have any NULL values. They are all empty strings.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

davdam8 Hall of Fame Points: 3620 More actions · Answer 4

U right

->

INSERT INTO #mytable

(ID, Value)

SELECT '4','First' UNION ALL

SELECT '37','Second'UNION ALL

SELECT '44','Second' UNION ALL

SELECT '54','Third' UNION ALL

SELECT '55','NULL' UNION ALL

SELECT '81','Fifth' UNION ALL

SELECT '86','NULL' UNION ALL

SELECT '96','NULL'

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

This returns the rows in the order you want according to your sample data.

drop table #mytable

create table #mytable

(

ID int,

Value varchar(20)

)

INSERT INTO #mytable

(ID, Value)

SELECT 4,'First' UNION ALL

SELECT 37,'Second'UNION ALL

SELECT 44,'Second' UNION ALL

SELECT 54,'Third' UNION ALL

SELECT 55,'' UNION ALL

SELECT 81,'Fifth' UNION ALL

SELECT 86,'' UNION ALL

SELECT 96,''

select ID, isnull(nullif(Value, ''), 'UnDefined')

from #mytable

order by case when nullif(Value, '') IS null then 0 else 1 end, ID

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

davdam8 (7/22/2013)
U right
->
INSERT INTO #mytable
(ID, Value)
SELECT '4','First' UNION ALL
SELECT '37','Second'UNION ALL
SELECT '44','Second' UNION ALL
SELECT '54','Third' UNION ALL
SELECT '55','NULL' UNION ALL
SELECT '81','Fifth' UNION ALL
SELECT '86','NULL' UNION ALL
SELECT '96','NULL'

Those are not nulls either. They are string literals. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

You still don't have NULL values on your sample data. However, check what I did with the alias for your column.

Select ISNULL(Value, 'Not Defined') as newValue

from #mytable

ORDER BY value

The problem is that SQL Server will try to order by the alias and not by the table's column.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

davdam8 Hall of Fame Points: 3620 More actions · Answer 8

Show Offfff... 🙂

I get the idea but it gives me:

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

davdam8 (7/22/2013)
Show Offfff... 🙂
I get the idea but it gives me:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator

Why do you need a UNION? In your example there is no need for a UNION. Try running the example I posted. It does not need a UNION and it returns the rows in the order you said you wanted them.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

davdam8 Hall of Fame Points: 3620 More actions · Answer 10

davdam8

Hall of Fame

Points: 3620

July 22, 2013 at 10:27 am

#1634567

U right man!

Thanks

baby steps, baby steps