Complex Ordering of a Record

# Thread: Complex Ordering of a Record

1. Member
Join Date
Dec 1969
Posts
79

## Complex Ordering of a Record

First some background:<BR>I&#039;ve got some records that I want to order by &#039;ProjectNumber&#039;, a project number is the last two digits of the year followed by three digits. So a project from 1997 would be 97xxx, one from this year is 02xxx.<BR><BR>Obviously when order by project number descending I would get all the 99xxx’s, 98xxx’s etc and then 02xxx’s, 01xxx’s, etc.<BR><BR>My question:<BR>Can I throw some sort of logic into my SQL statement to sort w/ the 2002 jobs first (and have it find the 03 jobs next year, and the 04 the year after that)?<BR><BR>Or:<BR>Should I just sort the records descending, loop through them to find the newest project (at position x), output the rest of the records, then go back to the first record (position 0) and output 0 to x-1?<BR><BR>Thanks,<BR>DJ<BR>

2. Senior Member
Join Date
Dec 1969
Posts
3,195

## If you are saying that you

want to order on ascending for the year portion but descending on the "three digit" portion then break of the order by:<BR><BR>SELECT projnum<BR> FROM projects<BR> ORDER BY substr(projnum,1,2) asc, substr(projnum,3,3) desc<BR><BR>I&#039;m assuming a char field, however you can easily change.<BR><BR>

3. Member
Join Date
Dec 1969
Posts
79

## I'm looking for...

...something like this.<BR><BR>02155<BR>02034<BR>01201<BR>00101<BR>9 9056<BR>98213<BR>98212

4. Senior Member
Join Date
Dec 1969
Posts
3,195

## Then convert the

2-digit "year" portion to a 4-digit year and sort on that.<BR><BR>So you&#039;re sorting on <BR><BR>2002155<BR>2002034<BR>2001201<BR>1999056<B R>etc.<BR><BR><BR><BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
1,032

## use something like

assuming your field to sort on is called "field" and contains<BR>2 digit year + 3 more digits <BR><BR>use something like ....<BR><BR>select field from mytable<BR>order by <BR>convert(datetime,&#039;01/01/&#039;+substring(field,1,2),4) desc ,convert(integer,substring(field,3,3)) asc<BR><BR><BR><BR>jon

6. Member
Join Date
Dec 1969
Posts
79

## RE: use something like

That looks like it&#039;s right on track, but i&#039;m having troube getting it to work in access. Any ideas?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•