Table values are as you shown: MyTable FieldA FieldB FieldC 1 2 3 1 2 <null> 1 <null> 2 2 3 4 2 <null> <null> <null> 2 3
On Thursday, 12 March 2015 6:32 PM, Vishal Tiwari <vishuals...@yahoo.co.in> wrote: Hi SET, I have already tried but still not getting the result. Last column displays Null value in middle of some rows. With Best Regards. Vishal On Thursday, 12 March 2015 6:26 PM, "Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]" <firebird-support@yahoogroups.com> wrote: >I am using Firebird database with 2.1 version. I have one issue in Order By clause. >I have SQL which gives multile records for multiple employees, i.e. one >employee has multiple records as an output in SQL. >I have Four columns in Order By Clause and I want every columns Null value >should be displayed after Not Null value in every column. >I googled and found that if I Use >Order By Column1 Asc Nulls Last >It works very well for the first columns but for the remaining two columns it >doesn't work i.e. for these two remaining columns the >Null value comes in middle i.e. first some values Not Null, then Null Values >and then again Not Null Values. >I need to display Null Values at the end of every Not Null value for every >Four columns specified in Order By Clause for every employee. >I tried Case When, then Asc for every column in Order By clause, but didn't >get the expected result. This type of question is often best asked with an example. Let's say you have these data: MyTable FieldA FieldB FieldC 1 2 3 1 2 <null> 1 <null> 2 2 3 4 2 <null> <null> <null> 2 3 To get the result in the order it has above, I would use ORDER BY FieldA NULLS LAST, FieldB NULLS LAST, FieldC NULLS LAST If you for some strange reason want THIS order (the columns with the largest number of nulls should be last): FieldA FieldB FieldC 1 2 3 2 3 4 1 2 <null> 1 <null> 2 <null> 2 3 2 <null> <null> then you need your CASE construct to be something like: ORDER BY IIF(FieldA IS NULL, 2, 0)+IIF(FieldB IS NULL, 3, 0)+IIF(FieldC IS NULL, 4, 0), FieldA, FieldB, FieldC If there are four fields to order by, you have to use minimum 3, 4, 5 and 6 rather than 2, 3 and 4. HTH, Set #yiv3095725044 -- #yiv3095725044ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3095725044 #yiv3095725044ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3095725044 #yiv3095725044ygrp-mkp #yiv3095725044hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3095725044 #yiv3095725044ygrp-mkp #yiv3095725044ads {margin-bottom:10px;}#yiv3095725044 #yiv3095725044ygrp-mkp .yiv3095725044ad {padding:0 0;}#yiv3095725044 #yiv3095725044ygrp-mkp .yiv3095725044ad p {margin:0;}#yiv3095725044 #yiv3095725044ygrp-mkp .yiv3095725044ad a {color:#0000ff;text-decoration:none;}#yiv3095725044 #yiv3095725044ygrp-sponsor #yiv3095725044ygrp-lc {font-family:Arial;}#yiv3095725044 #yiv3095725044ygrp-sponsor #yiv3095725044ygrp-lc #yiv3095725044hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3095725044 #yiv3095725044ygrp-sponsor #yiv3095725044ygrp-lc .yiv3095725044ad {margin-bottom:10px;padding:0 0;}#yiv3095725044 #yiv3095725044actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3095725044 #yiv3095725044activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3095725044 #yiv3095725044activity span {font-weight:700;}#yiv3095725044 #yiv3095725044activity span:first-child {text-transform:uppercase;}#yiv3095725044 #yiv3095725044activity span a {color:#5085b6;text-decoration:none;}#yiv3095725044 #yiv3095725044activity span span {color:#ff7900;}#yiv3095725044 #yiv3095725044activity span .yiv3095725044underline {text-decoration:underline;}#yiv3095725044 .yiv3095725044attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3095725044 .yiv3095725044attach div a {text-decoration:none;}#yiv3095725044 .yiv3095725044attach img {border:none;padding-right:5px;}#yiv3095725044 .yiv3095725044attach label {display:block;margin-bottom:5px;}#yiv3095725044 .yiv3095725044attach label a {text-decoration:none;}#yiv3095725044 blockquote {margin:0 0 0 4px;}#yiv3095725044 .yiv3095725044bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3095725044 .yiv3095725044bold a {text-decoration:none;}#yiv3095725044 dd.yiv3095725044last p a {font-family:Verdana;font-weight:700;}#yiv3095725044 dd.yiv3095725044last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3095725044 dd.yiv3095725044last p span.yiv3095725044yshortcuts {margin-right:0;}#yiv3095725044 div.yiv3095725044attach-table div div a {text-decoration:none;}#yiv3095725044 div.yiv3095725044attach-table {width:400px;}#yiv3095725044 div.yiv3095725044file-title a, #yiv3095725044 div.yiv3095725044file-title a:active, #yiv3095725044 div.yiv3095725044file-title a:hover, #yiv3095725044 div.yiv3095725044file-title a:visited {text-decoration:none;}#yiv3095725044 div.yiv3095725044photo-title a, #yiv3095725044 div.yiv3095725044photo-title a:active, #yiv3095725044 div.yiv3095725044photo-title a:hover, #yiv3095725044 div.yiv3095725044photo-title a:visited {text-decoration:none;}#yiv3095725044 div#yiv3095725044ygrp-mlmsg #yiv3095725044ygrp-msg p a span.yiv3095725044yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3095725044 .yiv3095725044green {color:#628c2a;}#yiv3095725044 .yiv3095725044MsoNormal {margin:0 0 0 0;}#yiv3095725044 o {font-size:0;}#yiv3095725044 #yiv3095725044photos div {float:left;width:72px;}#yiv3095725044 #yiv3095725044photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv3095725044 #yiv3095725044photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3095725044 #yiv3095725044reco-category {font-size:77%;}#yiv3095725044 #yiv3095725044reco-desc {font-size:77%;}#yiv3095725044 .yiv3095725044replbq {margin:4px;}#yiv3095725044 #yiv3095725044ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3095725044 #yiv3095725044ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3095725044 #yiv3095725044ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3095725044 #yiv3095725044ygrp-mlmsg select, #yiv3095725044 input, #yiv3095725044 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3095725044 #yiv3095725044ygrp-mlmsg pre, #yiv3095725044 code {font:115% monospace;}#yiv3095725044 #yiv3095725044ygrp-mlmsg * {line-height:1.22em;}#yiv3095725044 #yiv3095725044ygrp-mlmsg #yiv3095725044logo {padding-bottom:10px;}#yiv3095725044 #yiv3095725044ygrp-msg p a {font-family:Verdana;}#yiv3095725044 #yiv3095725044ygrp-msg p#yiv3095725044attach-count span {color:#1E66AE;font-weight:700;}#yiv3095725044 #yiv3095725044ygrp-reco #yiv3095725044reco-head {color:#ff7900;font-weight:700;}#yiv3095725044 #yiv3095725044ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3095725044 #yiv3095725044ygrp-sponsor #yiv3095725044ov li a {font-size:130%;text-decoration:none;}#yiv3095725044 #yiv3095725044ygrp-sponsor #yiv3095725044ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3095725044 #yiv3095725044ygrp-sponsor #yiv3095725044ov ul {margin:0;padding:0 0 0 8px;}#yiv3095725044 #yiv3095725044ygrp-text {font-family:Georgia;}#yiv3095725044 #yiv3095725044ygrp-text p {margin:0 0 1em 0;}#yiv3095725044 #yiv3095725044ygrp-text tt {font-size:120%;}#yiv3095725044 #yiv3095725044ygrp-vital ul li:last-child {border-right:none !important;}#yiv3095725044