Displaying NULL as the last row + ORDER BY
-
Hi All,
I have to display NULL in the last row eventhough the query which has ORDER BY clause.
Note : I don't want to use any temporary table or table variable.
#1:
SELECT N
from Tally
where N < 11
UNION ALL
SELECT NULL
Output:
N
-------
1
2
3
4
5
6
7
8
9
10
NULL
#2:
SELECT N
from Tally
where N < 11
UNION
SELECT NULL
Output:
N
-------
NULL
1
2
3
4
5
6
7
8
9
10
#3:
SELECT N
from Tally
where N < 11
UNION ALL
SELECT NULL
ORDER BY N
Output:
N
-------
NULL
1
2
3
4
5
6
7
8
9
10
I did the above R&D and noted the following points.
If our query format is like above....
1) ORDER BY display the NULL value in the first row.
2) UNION also display the NULL value in the first row.
Am i correct ?
My Query:
select SNo=count(*),
a.FinCategory as 'CATEGORY',
a.ProjectId AS 'PROJECT ID',
a.ProjectName AS 'PROJECT NAME',
a.Isbillable as 'BILLABLE',
a.Budget AS 'BUDGET',
a.ProjectActuals AS 'ACTUALS',
a.RForecast AS 'REMAINDER FORECAST',
a.PForecast AS 'FULL YEAR FORECAST',
a.Variance AS 'VARIANCE'
FROM FProject a, FProject b
where a.FinCategory + a.Isbillable + a.ProjectId
>= b.FinCategory + b.Isbillable + b.ProjectId
group by a.FinCategory, a.Isbillable ,a.ProjectId,a.ProjectName,a.BenBudget,a.ProjectActuals,a.ProjectForecast,
a.RForecast,a.Variance
Union all
Select count(*)+1 , 'TOTAL', ' ', ' ',' ', ISNULL(sum(BenBudget),0), ISNULL(sum(ProjectActuals),0),ISNULL(sum(RForecast),0), ISNULL(sum(ProjectForecast),0),ISNULL(sum(Variance),0)
FROM FProject order by SNO
I am sorry for not giving the sample table and data... Becuase this query was used by some other team..But they wanted to help me...so they send only this query.
karthik
-
Actually i didn't executed the above actual query... But i did the above R&D work to identify the problematic area.. also i found that the problem is ORDER BY clause...
what is the work around to display the NULL value in the last row evnthough the query has ORDERBY clause ?
karthik
-
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,N
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
So..I have to change the query like
select SNo,CATEGORY,PROJECT ID,PROJECT NAME,BILLABLE,BUDGET,ACTUALS,REMAINDER FORECAST,FULL YEAR FORECAST, VARIANCE
from
(select SNo=count(*),
a.FinCategory as 'CATEGORY',
a.ProjectId AS 'PROJECT ID',
a.ProjectName AS 'PROJECT NAME',
a.Isbillable as 'BILLABLE',
a.Budget AS 'BUDGET',
a.ProjectActuals AS 'ACTUALS',
a.RForecast AS 'REMAINDER FORECAST',
a.PForecast AS 'FULL YEAR FORECAST',
a.Variance AS 'VARIANCE'
FROM FProject a, FProject b
where a.FinCategory + a.Isbillable + a.ProjectId
>= b.FinCategory + b.Isbillable + b.ProjectId
group by a.FinCategory, a.Isbillable ,a.ProjectId,a.ProjectName,a.BenBudget,a.ProjectActuals,a.ProjectForecast,
a.RForecast,a.Variance
Union all
Select Null , 'TOTAL', ' ', ' ',' ', ISNULL(sum(BenBudget),0), ISNULL(sum(ProjectActuals),0),ISNULL(sum(RForecast),0), ISNULL(sum(ProjectForecast),0),ISNULL(sum(Variance),0)
FROM FProject ) X
order by CASE WHEN SNO IS NULL THEN 1 ELSE 0 END,SNO
karthik
-
1 Use ANSI joins
2 Avoid using single quotes around alias names
3 Dont sent summay information along with detail records
MadhivananFailing to plan is Planning to fail
-
ORDER BY CASE WHEN N IS NULL THEN 1 ELSE 0 END,N
How it is working ? I am unable to understand...
karthik
-
Another possible solution is:
SELECT * FROM (SELECT N
from Tally
where N < 11
ORDER BY N)
UNION ALL
SELECT NULL
ORDER BY used in UNION construction affects whole result set.
-
another solution
Is to put an isnull acount the field and just make the value really hight 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
Thanks for your inputs !
But still i am curious to know the logic used by the below code.
ORDER BY CASE WHEN N IS NULL THEN 1 ELSE 0 END,N
ORDER BY 1 -- means the result set will be ordered by the first column.
I am totally confused....
karthik
-
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
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
So you mean
1- non null values
0- null values
i.e
1 = Non Null = 1
2 = Non Null = 1
3 = Non Null = 1
4 = Non Null = 1
5 = Non Null = 1
6 = Non Null = 1
7 = Non Null = 1
8 = Non Null = 1
9 = Non Null = 1
10 = Non Null = 1
Null = Null = 0
But all these things are assigned by ORDER BY clause internally.
Am i correct ?
karthik
-
I'm not sure what you mean,
Put it this way.
write this code:
DECLARE @Tmp TABLE
(COL1 INT)
INSERT INTO @Tmp
SELECT null UNION ALL
SELECT TOP 10 n FROM Tally UNION ALL
SELECT null UNION ALL
SELECT null UNION ALL
SELECT 11
SELECT *
FROM @Tmp
SELECT
CASE WHEN COL1 IS NULL THEN 1 ELSE 0 END
,Col1
FROM @Tmp
ORDER BY
CASE WHEN COL1 IS NULL THEN 1 ELSE 0 END
,Col1
Oh I got my 1's and 0's mixed up but this should sow you whats happening
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
I have modified the code as below
SELECT N
FROM
(
SELECT N
from Tally
where N < 11
UNION ALL
SELECT NULL
) X
ORDER BY CASE WHEN N IS NULL THEN 3 ELSE 2 END,N
It also gave me the correct output.
SELECT N
FROM
(
SELECT N
from Tally
where N < 11
UNION ALL
SELECT NULL
) X
ORDER BY CASE WHEN N IS NULL THEN 4 ELSE 5 END,N
Output:
N
--
Null
1
2
3
4
5
6
7
8
9
10
query:
SELECT N
FROM
(
SELECT N
from Tally
where N < 11
UNION ALL
SELECT NULL
) X
ORDER BY CASE WHEN N IS NULL THEN 5 ELSE 4 END,N
Output:
N
---
1
2
3
4
5
6
7
8
9
10
Null
So I have noticed the below points.
1) if i give big number in the condition satisfied part, it is showing NULL in the last row.
EX: IS NULL THEN 5 ELSE 4
2) if i give small number in the condition satisfied part, it is showing NULL in the last row.
EX: IS NULL THEN 4 ELSE 5
Now i am lot more confused...what is happening internally ? how it is sorting the result set ?
karthik
-
Ok I think you need to realise that it's not sorting but
column 0 or column 1
It's sorting by the numbers 0 and 1 which means 0 always comes before 1.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
so in your example, if the null values get given 5 and the non-nulls get 4 then that shows
4 comes before 5
so non-nulls come before nulls.
does that make sense.
I think if you actually return the case statement to the screen you'll kick yourself cause it will make sense so fast 😉
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply