November 18, 2008 at 12:29 pm
Christopher,
Thanks for your explanation.
if we give like
CASE WHEN N IS NULL THEN 5 ELSE 4 END
it means
NonNull value will be treated as 4 ( Internally )
Null values will be trated as 5 (Internally)
So, As you said...4 comes before 5. so it sorts accordingly...Am i correct ? if it is correct then why we need this N.
CASE WHEN N IS NULL THEN 5 ELSE 4 END,N
I have tested the below code
SELECT N
FROM
(
SELECT N
from Tally
where N < 11
UNION ALL
SELECT NULL
) X
ORDER BY CASE WHEN N IS NULL THEN 1 ELSE 0 END
It gave me the correct output.
why we need that extra 'N' near to the ORDER BY clause ? is it required ? I think 'No'.
karthik
November 18, 2008 at 8:20 pm
Must look eye... let the code be the teacher...
SELECT [font="Arial Black"]CASE WHEN N IS NULL THEN 5 ELSE 4 END, N[/font]
FROM
(
SELECT N
from Tally
where N < 11
UNION ALL
SELECT NULL
) X
ORDER BY [font="Arial Black"]CASE WHEN N IS NULL THEN 5 ELSE 4 END,N[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2008 at 2:19 am
Thanks Jeff,
Like Jeff has just shown you , it's good to out put you order by so you can see the values.
The reason you still need to order by the n is because you want to order that column!
You tally table probably has a clustered index on it so the n is already ordered 1 ....1000
But if the column that contains nulls is not clustered then if you don't add the second order by how will you order that column?
Is this finally making sense?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 19, 2008 at 8:15 am
Chris, I think you have that backwards. I'd state it like this:
That order by clause is effectively creating a new field for the entire set of data, where the condition in the CASE statement determines it's value. This "new field" is then being used as part of the sorting process, as the first of the two sort fields, with N being the second. As the conditions of the CASE statement indicate that whenever N is NULL, the value of this field is 0, and is otherwise 1, any record where N has a value will be sorted on the basis of the 0 value, while N being NULL will sort on the basis of the 1 value. Net result is that the sorting field only has a value greater than 0 when N is NULL, and thus, the NULL value sorts last.
Steve
(aka smunson)
:):):)
Christopher Stobbs (11/18/2008)
NOPE.What is happening is it is ordering by
1,n
So all the value that are not null have 1 and then value as order by
so all the 1's come first then all the 0's come second.
Then once that is done then the Values are ordered.
but because all the 0's are null they are at the end!
Does that make sense?
Thanks
Chris
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 19, 2008 at 4:46 pm
"ORDER BY 5" sorts by column No.5, but when it's in an expression it's treated as a value, not column number.
_____________
Code for TallyGenerator
November 19, 2008 at 7:46 pm
Zactly... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2008 at 12:50 am
Thanks Chris and Jeff !
I know i can get some quality answers from here.Yes...My though is correct..I got the clear answers and from here...Hereafter I never forgot this concept...
But one doubt...
"ORDER BY 5" sorts by column No.5, but when it's in an expression it's treated as a value, not column number.
As sergiy mentioned,
ORDER BY 5 sorts by column no 5.
but...
ORDER BY CASE WHEN N IS NULL THEN N ELSE somecolumn name END
Won't be treated as an expression. Am i right ?
karthik
November 20, 2008 at 9:04 pm
Run the code... what do you think from running the code?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2008 at 6:23 am
I did the below testing.
SELECT CASE WHEN N IS NULL THEN N ELSE N END
FROM
(
SELECT N
from Tally
where N < 11
UNION ALL
SELECT NULL
) X
ORDER BY CASE WHEN N IS NULL THEN N ELSE N END,N
SELECT CASE WHEN N IS NULL THEN 1 ELSE N END
FROM
(
SELECT N
from Tally
where N < 11
UNION ALL
SELECT NULL
) X
ORDER BY CASE WHEN N IS NULL THEN 1 ELSE N END,N
SELECT CASE WHEN N IS NULL THEN 1 ELSE N END
FROM
(
SELECT N
from Tally
where N < 11
UNION ALL
SELECT NULL
) X
ORDER BY CASE WHEN N IS NULL THEN N ELSE 0 END,N
All of them showed NULL as the first row... so if we give N in the case statement, it will treated as column not an expression. I may be wrong..if yes, please clarifiy it.
But still i am not clear...Becuase i tested the below code....
SELECT N
from Tally
where N < 11
ORDER BY CASE WHEN N = 4 THEN 1 END,N
Output:
1
2
3
5
6
7
8
9
10
4
As per the code logic, if N = 4, then we are assigining value to 1, then what is the value for other numbers ( i.e 1,2,3,5,6,7,8,9,10)... how 4 is coming down ?
I am totally confused....
karthik
November 24, 2008 at 6:27 am
I also have executed the below code...
SELECT N,CASE WHEN N = 1 THEN 1 ELSE 2 END
from Tally
where N < 11
ORDER BY CASE WHEN N = 1 THEN 1 END,N
output:
22
32
42
52
62
72
82
92
102
11
How ? As per the rule, 2 should come after 1, but why it is coming before 1 ? :doze:
karthik
November 24, 2008 at 6:31 am
SELECT N,CASE WHEN N = 10 THEN 1 ELSE 2 END
from Tally
where N < 11
ORDER BY CASE WHEN N = 10 THEN 1 END,N
output:
22
32
42
52
62
72
82
92
102
11
karthik
November 24, 2008 at 8:38 am
Take a close look at the two case statements - they are NOT the same. The one in the select statement has an ELSE clause, while the one in the order by statement does not. That makes them very different, such that the one in the order by clause produces a NULL value for sorting unless N = the specified value, and thus the results you see are "expected".
Steve
(aka smunson)
:):):)
karthikeyan (11/24/2008)
SELECT N,CASE WHEN N = 10 THEN 1 ELSE 2 ENDfrom Tally
where N < 11
ORDER BY CASE WHEN N = 10 THEN 1 END,N
output:
22
32
42
52
62
72
82
92
102
11
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 25, 2008 at 1:51 am
ok...But my question was
SELECT N
from Tally
where N < 11
ORDER BY CASE WHEN N = 4 THEN 1 END,N
is showing 4 in the last row. How ?
what is happening internally ?
karthik
November 25, 2008 at 6:42 am
Because you've provided no ELSE clause for your CASE statement. The CASE statement in an ORDER BY clause, gives you the functional equivalent of adding a field to your output data, where the value of that field is determined by the result of the CASE statement, which is evaluated for every record that meets the WHERE criteria. That "new column's" resulting set of values is then used as a sort field. The problem with no ELSE clause, is that the value of the CASE statement for any value of N that is OTHER THAN the one you test for equality to, evaluates to NULL, because there's no part of the CASE statement to go to when N is any other value than the one specified, so NULL is assigned. That's an inherent behavior of the CASE statement. Does that help?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 26, 2008 at 2:30 am
...because there's no part of the CASE statement to go to when N is any other value than the one specified, so NULL is assigned...That's an inherent behavior of the CASE statement. Does that help?
Yes...This is what i wanted to know.
so the query assign NULL to all the values except for 4.
i.e internal values would be
1 Null
2 Null
3 Null
5 Null
6 Null
7 Null
8 Null
9 Null
10 Null
4 1
As per the rule, ORDER BY is displaying NULL values first. Am i correct ?
Now, I have few questions.
Is there any way to see that NULL value (which is assigned by default) externally?
I think the below one is not exactly related to the topic. but it may be.
ORDERBY will use the worktable to sort the data. TABLESCAN method will be used to fetch the data from the worktable. am i correct ?
Now...
1) How to know the size of worktable?
2) How to view the worktable ? i know...it is an internal process. But if there is any work around, i want to know that.
3) How to get the name of worktable? again, we need to refer the temp database. But i may wrong.
4) do we create or enforce indexes for worktable?
Inputs are welcome !
karthik
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply