order by clause
-
Hi All, I have one table containing few columns. LruName, ChName and Type. I want to fetch a query from the above table order by LruName, ChName, Type by using the above query I am getting output like this ------------------------------------------------------------------------------------------------------- LruName ChName Type ADU1 CH1 Tx ADU2 CH2 Tx AHRS1 CH3 Tx AHRS2 CH4 Tx DVS CH24 Rx DVS CH28 Rx DVS CH5 Tx DVS CH6 Tx FADEC1 CH1 Tx FADEC1 CH2 Tx FADEC2 CH3 Tx FADEC2 CH4 Tx NLDW CH8 Tx OAS CH7 Tx ---------------------------------------------------------------------------------------------------------- But I want output like this ---------------------------------------------------------------------------------------------------------- LruName ChName Type ADU1 CH1 Tx ADU2 CH2 Tx AHRS1 CH3 Tx AHRS2 CH4 Tx DVS CH5 Tx DVS CH6 Tx DVS CH24 Rx DVS CH28 Rx FADEC1 CH1 Tx FADEC1 CH2 Tx FADEC2 CH3 Tx FADEC2 CH4 Tx NLDW CH8 Tx OAS CH7 Tx ie I want LruName, Chname and Type to be order wise again in Type Tx ( rows ) should come first then Rx. how to write a query by getting the above results.
if the
Typefield is limited to the values in your example, then a descending order would do it, hence:order by LruName, ChName, Type DESC
:)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
if the
Typefield is limited to the values in your example, then a descending order would do it, hence:order by LruName, ChName, Type DESC
:)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
as
Tx > Rxand you want Tx first, descending should do it. :)Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
as
Tx > Rxand you want Tx first, descending should do it. :)Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
in your example the Type isn't really relevant to the sort as all (LruName,ChName) combinations are unique already without the Type field. So maybe you want
order by LruName, Type DESC, ChName
:)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
in your example the Type isn't really relevant to the sort as all (LruName,ChName) combinations are unique already without the Type field. So maybe you want
order by LruName, Type DESC, ChName
:)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Hi All, I have one table containing few columns. LruName, ChName and Type. I want to fetch a query from the above table order by LruName, ChName, Type by using the above query I am getting output like this ------------------------------------------------------------------------------------------------------- LruName ChName Type ADU1 CH1 Tx ADU2 CH2 Tx AHRS1 CH3 Tx AHRS2 CH4 Tx DVS CH24 Rx DVS CH28 Rx DVS CH5 Tx DVS CH6 Tx FADEC1 CH1 Tx FADEC1 CH2 Tx FADEC2 CH3 Tx FADEC2 CH4 Tx NLDW CH8 Tx OAS CH7 Tx ---------------------------------------------------------------------------------------------------------- But I want output like this ---------------------------------------------------------------------------------------------------------- LruName ChName Type ADU1 CH1 Tx ADU2 CH2 Tx AHRS1 CH3 Tx AHRS2 CH4 Tx DVS CH5 Tx DVS CH6 Tx DVS CH24 Rx DVS CH28 Rx FADEC1 CH1 Tx FADEC1 CH2 Tx FADEC2 CH3 Tx FADEC2 CH4 Tx NLDW CH8 Tx OAS CH7 Tx ie I want LruName, Chname and Type to be order wise again in Type Tx ( rows ) should come first then Rx. how to write a query by getting the above results.
One of the problems you are going to have is with the ChName column. It appears as though you want CH5 to appear before CH24. In your order by clause you may have to have it
order by .... ,length(ChName),ChName, ...
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
One of the problems you are going to have is with the ChName column. It appears as though you want CH5 to appear before CH24. In your order by clause you may have to have it
order by .... ,length(ChName),ChName, ...
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
One of the problems you are going to have is with the ChName column. It appears as though you want CH5 to appear before CH24. In your order by clause you may have to have it
order by .... ,length(ChName),ChName, ...
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
That's a good solution for a common problem. A five from me even if it wasn't what the OP wanted.
-
That's a good solution for a common problem. A five from me even if it wasn't what the OP wanted.
Thanks Jörgen.
Jörgen Andersson wrote:
even if it wasn't what the OP wanted.
Oddly, in the original post he indicated what output he was after and so I'm pretty sure he is going to have to do something similar to what I offered.
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Thanks Jörgen.
Jörgen Andersson wrote:
even if it wasn't what the OP wanted.
Oddly, in the original post he indicated what output he was after and so I'm pretty sure he is going to have to do something similar to what I offered.
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
I thought so too, and was contemplating looking up a stringsplit function that does the same job in a much more tortuous way.
-
That's a good solution for a common problem. A five from me even if it wasn't what the OP wanted.
I have a feeling it's only going to work if all the name values start with the same prefix. Supposing I have: A1 A20 A999 C1 C99 Sorting them by length(name), name would give: A1 C1 A20 C99 A999 So it would solve the OP's specific problem where all names appear to start with CH but I'm not sure it works as a general solution.
-
I have a feeling it's only going to work if all the name values start with the same prefix. Supposing I have: A1 A20 A999 C1 C99 Sorting them by length(name), name would give: A1 C1 A20 C99 A999 So it would solve the OP's specific problem where all names appear to start with CH but I'm not sure it works as a general solution.
I believe you're quite correct. We have exactly this issue and have solved it with a separate sortcolumn. But this isn't always a viable solution, and then one has to build a natural sort function[^].
-
Hi All, I have one table containing few columns. LruName, ChName and Type. I want to fetch a query from the above table order by LruName, ChName, Type by using the above query I am getting output like this ------------------------------------------------------------------------------------------------------- LruName ChName Type ADU1 CH1 Tx ADU2 CH2 Tx AHRS1 CH3 Tx AHRS2 CH4 Tx DVS CH24 Rx DVS CH28 Rx DVS CH5 Tx DVS CH6 Tx FADEC1 CH1 Tx FADEC1 CH2 Tx FADEC2 CH3 Tx FADEC2 CH4 Tx NLDW CH8 Tx OAS CH7 Tx ---------------------------------------------------------------------------------------------------------- But I want output like this ---------------------------------------------------------------------------------------------------------- LruName ChName Type ADU1 CH1 Tx ADU2 CH2 Tx AHRS1 CH3 Tx AHRS2 CH4 Tx DVS CH5 Tx DVS CH6 Tx DVS CH24 Rx DVS CH28 Rx FADEC1 CH1 Tx FADEC1 CH2 Tx FADEC2 CH3 Tx FADEC2 CH4 Tx NLDW CH8 Tx OAS CH7 Tx ie I want LruName, Chname and Type to be order wise again in Type Tx ( rows ) should come first then Rx. how to write a query by getting the above results.
just the following,-
order by LruName, ChName, Type DESC