Subject Re: [firebird-support] How To Display Null Values At the End For More Than One Columns Specified in Order By Clause?
Author
Post date 2015-03-14T13:25:18Z
Hi,
 
i found the way but for firebird3
 
SELECT A.A, A.NR, B.B, B.NR, C.C, C.NR, D.D, D.NR, E.E, E.NR
FROM
               (SELECT ROW_NUMBER() OVER() AS NR, A FROM (SELECT A FROM XXX ORDER BY A Asc Nulls Last)) A
FULL JOIN (SELECT ROW_NUMBER() OVER() AS NR, B FROM (SELECT B FROM XXX ORDER BY B Asc Nulls Last)) B ON A.NR=B.NR
FULL JOIN (SELECT ROW_NUMBER() OVER() AS NR, C FROM (SELECT C FROM XXX ORDER BY C Asc Nulls Last)) C ON B.NR=C.NR
FULL JOIN (SELECT ROW_NUMBER() OVER() AS NR, D FROM (SELECT D FROM XXX ORDER BY D Asc Nulls Last)) D ON C.NR=D.NR
FULL JOIN (SELECT ROW_NUMBER() OVER() AS NR, E FROM (SELECT E FROM XXX ORDER BY E Asc Nulls Last)) E ON D.NR=E.NR
 
Order By A.NR, A.A Asc Nulls Last, B.NR, B.B Asc Nulls Last, C.C Asc Nulls Last, D.D Asc Nulls Last, E.E Asc Nulls Last
 
regards,
Karol Bieniaszewski
 
 
Sent: Saturday, March 14, 2015 1:30 PM
Subject: Re: [firebird-support] How To Display Null Values At the End For More Than One Columns Specified in Order By Clause?
 
 

Hi,
 
look at this sample
 
CREATE TABLE XXX
(
  A varchar(20),
  B varchar(20),
  C varchar(20),
  D varchar(20),
  E varchar(20)
);
 
INSERT INTO XXX (A, B, C, D, E) VALUES ('AAA', 'BBB', NULL, 'CCC', 'DDD');
INSERT INTO XXX (A, B, C, D, E) VALUES ('VVV', NULL, 'CCC', 'EEE', NULL);
INSERT INTO XXX (A, B, C, D, E) VALUES ('EEE', '333', 'sdfs', 'asdas', NULL);
INSERT INTO XXX (A, B, C, D, E) VALUES ('EEE', '333', NULL, 'asdas', NULL);
INSERT INTO XXX (A, B, C, D, E) VALUES ('ttt', '444', NULL, 'asdas', '555');
 
commit;
 
SELECT case
  when A is not null
   and B is not  null
   and C is not  null
   and D is not  null
   and E is not  null
     then 0 else 1
end as scol,X.A, X.B, X.C, X.D, X.E
FROM XXX X
Order By scol, X.A Asc Nulls Last, X.B Asc Nulls Last, X.C Asc Nulls Last, X.D Asc Nulls Last, X.E Asc Nulls Last
 
 
and result of this query
AAA    BBB    [null]    CCC    DDD
VVV    [null]    CCC    EEE    [null]
EEE    333    sdfs    asdas    [null]
EEE    333    [null]    asdas    [null]
ttt    444    [null]    asdas    555
 
where do you see that maining is the same?
 
regards,
Karol Bieniaszewski
 
 
Sent: Saturday, March 14, 2015 5:03 AM
Subject: Re: Odp: [firebird-support] How To Display Null Values At the End For More Than One Columns Specified in Order By Clause?
 
 

Meaning was same.
 


On Friday, 13 March 2015 11:06 PM, "'liviuslivius@...' liviuslivius@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:


 
Hi,

Following stackoverflow i see that
this is not what you show us in sample table results

Regards,
Karol Bieniaszewski



----- Reply message -----
Od: "Vishal Tiwari vishualsoft@... [firebird-support]" <firebird-support@yahoogroups.com>
Do: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Temat: [firebird-support] How To Display Null Values At the End For More Than One Columns Specified in Order By Clause?
Data: pt., mar 13, 2015 12:53


 
Hi SET,

I got the answer from below URL.

 
Thank You for putting your efforts and sorry for my incomplete information.
 
With Best Regards.
 
Vishal


On Friday, 13 March 2015 12:45 PM, "Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:


 
Thanks for the SQL code, Vishal, you forgot to include
 
b) (Parts of) the result you get
c) The result you want
 
Both of these are necessary to find out how to write the required SQL.
 
Set