SQL problem, help needed

[LucieLastic]

hi All

I have a table containing the following:
connr outfltnr retfltnr day wkcap
021-W01 JMC117K JMC117L Sat 65
021-W01 JMC127K JMC127L Sat 60
021-W01 JMC133K JMC133L Sun 185
021-W01 JMC366K JMC366L Sun 80
021-W01 JMC366K Sun 80
021-W01 JMC419K JMC419L Sun 50
021-W01 JMC419K JMC419L Sun 50
021-W01 JMC575L Sun 30
021-W01 JMC475K JMC475L Sun 50
021-W01 JMC546K JMC546L Sun 60
021-W01 JMC546K JMC546L Sun 60
021-W01 JMC575K JMC575L Sun 30
021-W01 JMC366L Sun 110
021-W01 JMC634K JMC634L Sun 110
021-W01 JMC475L Sun 50
021-W01 JMC133L Sun 185
021-W01 JMC127L Sat 60
021-W01 JMC634L Sun 110


How can I construct a Select that will group my flight numbers together? That is, if you look at JMC634K/L, I need the last row (with no outbound flt) to be under the row containing both the outbound and inbound flt numbers. Same goes for JMC575K/L and JMC266K/L etc. The result would then appear :

connr outfltnr retfltnr day wkcap
021-W01 JMC117K JMC117L Sat 65
021-W01 JMC127K JMC127L Sat 60
021-W01 JMC127L Sat 60
021-W01 JMC133K JMC133L Sun 185
021-W01 JMC133L Sun 185
021-W01 JMC366K JMC366L Sun 80
021-W01 JMC366K Sun 80
021-W01 JMC366L Sun 110
021-W01 JMC419K JMC419L Sun 50
021-W01 JMC419K JMC419L Sun 50
021-W01 JMC475K JMC475L Sun 50
021-W01 JMC475L Sun 50
021-W01 JMC546K JMC546L Sun 60
021-W01 JMC546K JMC546L Sun 60
021-W01 JMC575K JMC575L Sun 30
021-W01 JMC575L Sun 30
021-W01 JMC634K JMC634L Sun 110
021-W01 JMC634L Sun 110


Is it possible to do this?

big ta
lou

 

[tlbroadbent]

Try this if outfltnr equals space or is an empty string.

Order By
connr,
retfltnr,
Case When rtrim(outfltnr)='' Then 'ZZZZZZZ' Else outfltnr End

If outfltnr is null then you can use this Order By clause.

Order By connr, retfltnr, isnull(outfltnr,'ZZZZZZZ')

Terry

X-) "Life would be easier if I had the source code." -Anonymous