Re: Help - Help on SQL
If you can live with a substitute character, the following,
although it is not pretty, seems to work. I used the tilde "~"
because it sorts high.
select "~", colb, colc from reseq
where cola is null and colb is not null and colc is not null
union
select colb, "~", colc from reseq
where colb is null and cola is not null and colc is not null
union
select cola, colb, "~" from reseq
where colc is null and cola is not null and colb is not null
unionselect "~", "~", colc from reseq
where cola is null and colb is null and colc is not null
union
select cola, "~", "~" from reseq
where cola is not null and colb is null and colc is null
unionselect "~", colb, "~" from reseq
where cola is null and colb is not null and colc is null
union
select cola, colb, colc from reseq
where cola is not null and colb is not null and colc is not null
order by 1,2,3
RESULT
(constant) colb colc
A A A
A A B
A B A
~ A A
~ A B
~ B A
~ ~ A
~ ~ B
Good luck
--
Con Woodall Colorado St. U.; Veterinary Teach. Hosp.; Ft. Collins CO 80523,
USofA; 970-491-1244 FAX 970-491-1205 cwoodall@vth1.vth.colostate.edu
"Ask me about my vow of silence!"
--
On Tue, 19 Dec 1995 /G=T./S=Kalyan/@nsc.sprint.com wrote:
} I got a couple of replies for my question below which had asked
} me to do a DESC order. I am sorry I did'nt write my question
} properly. I want the resultant set to be ordered ASC but the null
} values ( should in some way assume the highest value ) should
} appear at the bottom.
}
} If you take a look at the resultant set. It is in ASC order the
} only difference being that the NULL values have taken on the
} highest values and appears at the bottom.
}
} Sorry for being vague when I asked my question.Thanks for the
} responses.
}
} Thanks,
} Kalyan.
} ______________________________ Forward Header __________________________________
} Subject: Help on SQL
} Author: T. Kalyan at North-Supply
} Date: 12/18/95 2:34 PM
}
}
} The result set we get for ...
}
} SELECT col1, col2, col3 FROM tbl1 ORDER BY col1, col2, col3
}
} ... is as shown below :
}
}
} col1 col2 col3
} ^^^^ ^^^^ ^^^^
} Null Null A
} Null Null B
} Null A A
} Null A B
} Null B A
} A A A
} A A B
} A B A
}
} Is it possible to write a SQL so that the NULL values are pushed
} to the bottom and the ordering remains the same.
}
} The resultant set should be as shown below.
}
} col1 col2 col3
} ^^^^ ^^^^ ^^^^
} A A A
} A A B
} A B A
} Null A A
} Null A B
} Null B A
} Null Null A
} Null Null B
}
} Thanks,
} Kalyan.
}