By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,610 Members | 2,310 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,610 IT Pros & Developers. It's quick & easy.

Order By values of a column

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

P: n/a
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?

Nov 12 '05 #5

P: n/a
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?
Nov 12 '05 #6

P: n/a
DB2 version 7

Nov 12 '05 #7

P: n/a
Sorry for the incomplete info, I am using DB2 versions 7 on OS390.

Nov 12 '05 #8

P: n/a
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.
Nov 12 '05 #9

P: n/a
ORDER BY POSSTR('EB ' || 'TVD ' || 'DIFFDE' || 'Bob ', col2) / 6

Nov 12 '05 #10

P: n/a
sweet and smart, nice Tomkuma

Nov 12 '05 #11

P: n/a
None of the functions like POSSTR, TRANSLATE, LOCATE or CASE statement
seems to be working with V7

Nov 12 '05 #12

P: n/a
POSSTR is definitely available in V7 zOS.

what error message do you get ?

Nov 12 '05 #13

P: n/a
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

Nov 12 '05 #14

P: n/a
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

Nov 12 '05 #15

P: n/a
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.

Nov 12 '05 #16

P: n/a
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.

Nov 12 '05 #17

P: n/a
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

Nov 12 '05 #18

P: n/a
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.

Nov 12 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.