Order By values of a column

[singlal]

Hi,

I want the result of my query to be returned in a specific order based
upon values of a column. It will be more clear with example. Let's say
my table is as below

Col1 Col2 Col3
1 A Mark
2 A Rob
3 C Chris
4 B Tom
5 C Harry

I want the result to be returned sorted on Col2 in the order of values
B, C and A. So, I want to get it sorted as:

Col1 Col2 Col3
4 B Tom
3 C Chris
5 C Harry
1 A Mark
2 A Rob

Is this possible? How?

Thanks in advance,
Lalit

[Bernd Hohmann]

singlal wrote:

Is this possible? How?

select * from .... where .... order by col2 ASC

(or DESC for descending order)

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Ian]

singlal wrote:

Hi,

I want the result of my query to be returned in a specific order based
upon values of a column. It will be more clear with example. Let's say
my table is as below

Col1 Col2 Col3
1 A Mark
2 A Rob
3 C Chris
4 B Tom
5 C Harry

I want the result to be returned sorted on Col2 in the order of values
B, C and A. So, I want to get it sorted as:

select ...
order by
case col2
when 'B' then 1
when 'C' then 2
when 'A' then 3
else 4
end

[Bob Stearns]

singlal wrote:

Hi,

I want the result of my query to be returned in a specific order based
upon values of a column. It will be more clear with example. Let's say
my table is as below

Col1 Col2 Col3
1 A Mark
2 A Rob
3 C Chris
4 B Tom
5 C Harry

I want the result to be returned sorted on Col2 in the order of values
B, C and A. So, I want to get it sorted as:

Col1 Col2 Col3
4 B Tom
3 C Chris
5 C Harry
1 A Mark
2 A Rob

Is this possible? How?

Thanks in advance,
Lalit

order by translate(col2,'312','ABC'), col1

[singlal]

Thanks for your replies. Below are my problems:

While running with CASE logic written by IAN, I am getting SQLCODE
-214.

For Bob's translate function. it will work if I really had col2 with
length as one character. But I actually have values in col2 like 'EB',
'TVD', DIFFDE' etc. Bob, any clues on that?

[Gert van der Kooij]

In article <11**********************@g44g2000cwa.googlegroups .com>,
singlal (la************@gmail.com) says...

Thanks for your replies. Below are my problems:

While running with CASE logic written by IAN, I am getting SQLCODE
-214.

For Bob's translate function. it will work if I really had col2 with
length as one character. But I actually have values in col2 like 'EB',
'TVD', DIFFDE' etc. Bob, any clues on that?

Which DB2 version on which OS are you using?

[singlal]

DB2 version 7

[singlal]

Sorry for the incomplete info, I am using DB2 versions 7 on OS390.

[Darin McBride]

singlal wrote:

Thanks for your replies. Below are my problems:

While running with CASE logic written by IAN, I am getting SQLCODE
-214.

For Bob's translate function. it will work if I really had col2 with
length as one character. But I actually have values in col2 like 'EB',
'TVD', DIFFDE' etc. Bob, any clues on that?

First thing popping into my head was to create a table which mapped col2 to
numerical weights, joining with that, and ordering based on the weights.
Allows you to separate out the data (col2 <==> order/weight) from the
logic, meaning that if you want to tweak the order, or add new types, you
can do it outside of your code.

[Tonkuma]

ORDER BY POSSTR('EB ' || 'TVD ' || 'DIFFDE' || 'Bob ', col2) / 6

[juliane26]

sweet and smart, nice Tomkuma

[singlal]

None of the functions like POSSTR, TRANSLATE, LOCATE or CASE statement
seems to be working with V7

[juliane26]

POSSTR is definitely available in V7 zOS.

what error message do you get ?

[juliane26]

Hope you are still following:

POSSTR() is not accepting a column name on the second position.
Use LOCATE instead, that does accept columns on both positions. Be
aware that the order of the arguments is the other way round, so the
statement should be:

ORDER BY LOCATE(cols, 'EB ' || 'TVD ' || 'DIFFDE' || 'Bob ') / 6

Promise that does the job.

SELECT DEPTNO, LOCATE(DEPTNO,'B01' || 'A00' || 'C01' ||
'D01' || 'D11' || 'D21' || 'E01' || 'E11' || 'E21')
FROM XXXXXXXX.DEPT
ORDER BY 2

returns:

DEPTNO COL1
------ -----------
B01 1
A00 4
C01 7
D01 10
D11 13
D21 16
E01 19
E11 22
E21 25

[juliane26]

Hope you are still following:

POSSTR() is not accepting a column name on the second position.
Use LOCATE instead, that does accept columns on both positions. Be
aware that the order of the arguments is the other way round, so the
statement should be:

ORDER BY LOCATE(cols, 'EB ' || 'TVD ' || 'DIFFDE' || 'Bob ') / 6

Promise that does the job.

SELECT DEPTNO, LOCATE(DEPTNO,'B01' || 'A00' || 'C01' ||
'D01' || 'D11' || 'D21' || 'E01' || 'E11' || 'E21')
FROM XXXXXXXX.DEPT
ORDER BY 2

returns:

DEPTNO COL1
------ -----------
B01 1
A00 4
C01 7
D01 10
D11 13
D21 16
E01 19
E11 22
E21 25

[Brian Tkatch]

If none of the other solutions work well:

SELECT Col1, Col2, Col3, 1 Sort_Order FROM Table WHERE Col2 = 'B'
UNION ALL
SELECT Col1, Col2, Col3, 2 Sort_Order FROM Table WHERE Col2 = 'C'
UNION ALL
SELECT Col1, Col2, Col3, 3 Sort_Order FROM Table WHERE Col2 = 'A'
ORDER BY 4

B.

[Tonkuma]

singlal wrote "I am using DB2 versions 7 on OS390."
It is written in DB2 UDB SQL Reference Version 7 that a column name can
be used as search-string for POSSTR.
But, I failed in another query to use a column as search-string.
So, you are right. Perhaps manual's bug.

[juliane26]

Well,

for z/OS there is an own SQL reference, which states it is not
possible.
I got confused as well, and I was surprised it works with locate :-)

See V8 zOS docs:
http://publib.boulder.ibm.com/infoce...jnrmstr280.htm

[Tonkuma]

I saw your referenced doc and PDF version of SQL Reference Version 8.
Column name was removed from search-string for POSSTR. I guess manual
bug of Version 7 was corrected.
For LOCATE, column name is included usable expression for
search-string in both docs.
So, your result is consist with Version 8 manual.