>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