Home » SQL & PL/SQL » SQL & PL/SQL » Order by column value
Order by column value [message #193713] Tue, 19 September 2006 02:05 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
Can any one help me in getting a query with a column variable which is used for reference.
here is the data
Id	entered_dr	entered_cr	reference_1
1	15000				line-3
1			15000		line-6
1	15000				line-1
1	15000				line-5
1			15000		line-2
1			15000		line-4
86	27770				line-6
86			15000		line-3
86	15000				line-2
86			15000		line-1
86			27770		line-5
86	15000				line-4

I want to do a sort with the reference_1 column , as tag number is there after word line- ie result should be like wise
Id	entered_dr	entered_cr	reference_1
1	15000				line-1
1			15000		line-2
1	15000				line-3
1			15000		line-4
1	15000				line-5
1			15000		line-6
86			15000		line-1
86	15000				line-2
86			15000		line-3
86	15000				line-4
86			27770		line-5
86	27770				line-6


Is there any way in sql we can sort the data on first Id then the tag counter of field Reference_1.

Thanks to everyone in advance
Re: Order by column value [message #193717 is a reply to message #193713] Tue, 19 September 2006 02:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SELECT...
ORDER BY id,to_number(substr(reference_1,6))


Re: Order by column value [message #193728 is a reply to message #193717] Tue, 19 September 2006 02:51 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
order by id, reference_1
Re: Order by column value [message #193732 is a reply to message #193728] Tue, 19 September 2006 03:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just ordering by reference_1 will order the values as strings, not numbers,
ie you'd get
LINE-1
LINE-10
LINE-11
LINE-2
LINE-20
LINE-21

wheras I think the OP wants
LINE-1
LINE-2
LINE-10
LINE-11
LINE-20
LINE-21
Re: Order by column value [message #193733 is a reply to message #193732] Tue, 19 September 2006 03:22 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
You are absolutely correct Smile
Wonder why they store the "line-" string anyway...

Re: Order by column value [message #193735 is a reply to message #193733] Tue, 19 September 2006 03:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Who knows?
You could go quietly mad wondering why people have done some of the the things we've seen round here.

Then again, for all I know, there's people working on my old systems cursing me for doing things that seemed good at the time. Cool
Re: Order by column value [message #193736 is a reply to message #193735] Tue, 19 September 2006 03:40 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Laughing You are right - again
Previous Topic: cursor paramater and like clause
Next Topic: Date format
Goto Forum:
  


Current Time: Sun Dec 11 08:08:15 CST 2016

Total time taken to generate the page: 0.09068 seconds