>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
Actually, Karol, since all your rows contain at least one NULL, I'd say the
result of your query would be identical to the result if there were no scol
column (providing a case insensitive collation was used and we're thinking
English alphabet):
1 AAA BBB [null] CCC DDD
1 EEE 333 sdfs asdas [null]
1 EEE 333 [null] asdas [null]
1 ttt 444 [null] asdas 555
1 VVV [null] CCC EEE [null]
Generally, the above select is sensible if the only idea is to put rows
containing one or more nulls below rows that contain values in all rows.
However, if the idea is to put the rows with the most nulls furthermost down, I
would rather recommend:
SELECT iif(A is null, 1, 0)+iif(B is null, 1, 0)+iif(C is null, 1, 0)+iif(D is
null, 1, 0)+iif(E is null, 1, 0) as scol, A, B, C, D, E
FROM XXX
Order By 1, A Asc Nulls Last, B Asc Nulls Last, C Asc Nulls Last, D Asc Nulls
Last, E Asc Nulls Last
with your example, that would yield this result:
1 AAA BBB [null] CCC DDD
1 EEE 333 sdfs asdas [null]
1 ttt 444 [null] asdas 555
2 EEE 333 [null] asdas [null]
2 VVV [null] CCC EEE [null]
i.e.
1 ttt 444 [null] asdas 555
with its one null would come before
2 EEE 333 [null] asdas [null]
since it has two nulls. One thing it doesn't cater for, is if row with early
null column should be ordered before row with later null column (imagine ttt
changing to BBB, then that would be ordered before EEE despite EEE having null
in E whereas BBB would have the null in C. It should be feasible to do this if
desired, I guess it could be as simple as changing to
SELECT iif(A is null, 1, 0)+iif(B is null, 1, 0)+iif(C is null, 1, 0)+iif(D is
null, 1, 0)+iif(E is null, 1, 0) as scol,
iif(A is null, 16, 0)+iif(B is null, 8, 0)+iif(C is null, 4, 0)+iif(D is
null, 2, 0)+iif(E is null, 1, 0) as scol2,
A, B, C, D, E
FROM XXX
Order By 1, 2, A, B, C, D, E
The main thing is of course that Vishal found a solution that he finds
satisfactory, it doesn't matter whether it was here or somewhere else he found
it (though I do admit it was a bit frustrating with a problem description that
didn't explain what result he got and how that differed from what he wanted,
making it almost impossible for us to give him a useful answer).
Set
