Gossamer Forum
Home : General : Databases and SQL :

Order by last three numbers of five numbers

 
Order by last three numbers of five numbers
Ok I this this is really simple. I am new to SQL. I am trying to use the order by to have jobs listed on a webpage. It works great but, when the company started out the owners came up with a job number scheme that used year and then a three digit number. Example: 85001 (YYJOB) The jobs sort great until 2000. 00701 appears before 85001. How can I sort the information excluding the first two digits.
 
Re:Update here is what I have so far In reply to
SELECT *
FROM dbo.Job_Information
ORDER BY Job_Number ASC
 
Re: [briski00] Re:Update here is what I have so far In reply to
Add a Primary Key column, then order the results by that column -OR- if there is a date (timedatestamp) column in the table, order by that by descending order....

Like:

ORDER BY DateAdded DESC

OR

ORDER BY PKField
========================================
Buh Bye!

Cheers,
Me
 
Re: [Stealth] Re:Update here is what I have so far In reply to
Humm thats really my issue. I extract the information from another program. I added the column previously but creates other issues. Time Stamp.. been there done that but the job are not input into the system in order. I have been trying something like this...

This is part of the table You can see the 2001 year before the 1987 year of course 2000, 2001, 2002 and 2003 are all before the 1987.



01773 Taylorsville Senior Center
87001 T-Jay Studio
87002 Travel Parks-West Town Center
87003 State Firm-West Town Center
87004 Magic Mill-Cottonwood Village
87005 Communication World
87006 Coast Guard-Westgate Plaza
87007 Travel Perks II-West Center
87008 Medical Office-Westgate


I have tried this

SELECT *
FROM JOB_INFORMATION
ORDER BY SUBSTR(JOB_NUMBER,3,3) ASC;

but get this

'SUBSTR' is not a recognized function name.
 
Re: [briski00] Re:Update here is what I have so far In reply to
Hi

Try

SELECT *
FROM JOB_INFORMATION
ORDER BY RIGHT(JOB_NUMBER,3)

You can play around with this.

Dregs2
 
Re: [dregs2] Re:Update here is what I have so far In reply to
whoo hoo!! TY BRO that works great!!
 
Re: [Stealth] Re:Update here is what I have so far In reply to
Keep the bickering out of the forum. I've removed the posts and locked this thread.

Alex
--
Gossamer Threads Inc.

Last edited by:

Alex: Feb 21, 2003, 11:33 AM