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.
Feb 20, 2003, 9:31 AM
Veteran (17240 posts)
Feb 20, 2003, 9:31 AM
Post #3 of 7
Views: 8398
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
Like:
ORDER BY DateAdded DESC
OR
ORDER BY PKField
========================================
Buh Bye!
Cheers,
Me
Feb 20, 2003, 2:54 PM
Novice (5 posts)
Feb 20, 2003, 2:54 PM
Post #4 of 7
Views: 8347
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.
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.
Feb 21, 2003, 11:33 AM
Administrator (9387 posts)
Feb 21, 2003, 11:33 AM
Post #7 of 7
Views: 8235
Keep the bickering out of the forum. I've removed the posts and locked this thread.
Alex
--
Gossamer Threads Inc.
Alex
--
Gossamer Threads Inc.