Retrieve most recent records from database sorted by string value

[Randy Jackson]

First of all, I apologize in advance if this is covered somewhere in a
FAQ. I did a Google search, but really couldn't find anything.

I'm having a problem with a simple select query. I've got users
uploading data into a database. I want to do something that seems as if
it should be really simple, but it isn't so far.

Basically, I want to retrieve the latest 12 records from the table,
sorted by the date-time field (uploaddate) then by a varchar field
(name). Here's the SQL that I'm using:

select * from tblcontent
where filetypeid=1 and release='Y'
order by uploaddate desc, name limit 12;

And here's the table schema:

Field,Type,Null ,Key,Default,Ex tra
ContentID,int(3 ),,PRI,NULL,aut o_increment
Name,varchar(50 ),,,,
Filename,varcha r(100),,,,
Thumbnail,varch ar(100),,,,
FileTypeID,int( 11),,,0,
UploadDate,date time,,,0000-00-00 00:00:00,
Description,tex t,,,,
ContributorID,i nt(10) unsigned,,,0,
Release,enum('Y ','N'),YES,,N,

The problem I'm running into is that while I am retrieving the most
recent 12 records, it's not sorting by the varchar field. Instead, the
'Name' field seems randomly sorted. If I change the sort order (name,
uploaddate desc), I don't get the records that I want.

This seems like it should be something very simple to do, but it's
driving me nuts and I really have no idea how to resolve the problem. I
know I'm probably doing something really stupid, but at this point I need
help.

I appreciate anyone's patience who's read through all of this, and any
answer will be most appreciated.

[Rick Lones]

Re: Retrieve most recent records from database sorted by string value

Randy Jackson wrote:
[color=blue]
> Basically, I want to retrieve the latest 12 records from the table,
> sorted by the date-time field (uploaddate) then by a varchar field
> (name). Here's the SQL that I'm using:
>
> select * from tblcontent
> where filetypeid=1 and release='Y'
> order by uploaddate desc, name limit 12;[/color]

Rows which sort equal on the first field are then ordered within the
duplicates by the second sort field, i.e. the sorts are "nested". What
you really want is two independent sorts done sequentially. So I think
you need two queries. If you are using a version of mysql which
supports sub-selects (4.1 or above, I believe) then you can nest your
entire query more or less as given within an outer query as per:

select q1.* from
(
select * from tblcontent
where filetypeid=1 and release='Y'
order by uploaddate desc limit 12
) as q1
order by q1.name

HTH,
-rick-

[Randy Jackson]

Re: Retrieve most recent records from database sorted by string value

Rick Lones <rlones@charter .net> wrote in news:10jn9hltio 76hc7
@corp.supernews .com:
[color=blue]
> Rows which sort equal on the first field are then ordered within the
> duplicates by the second sort field, i.e. the sorts are "nested". What
> you really want is two independent sorts done sequentially. So I think
> you need two queries. If you are using a version of mysql which
> supports sub-selects (4.1 or above, I believe) then you can nest your
> entire query more or less as given within an outer query as per:
>
> select q1.* from
> (
> select * from tblcontent
> where filetypeid=1 and release='Y'
> order by uploaddate desc limit 12
> ) as q1
> order by q1.name
>
> HTH,
> -rick-[/color]

Sadly, I'm stuck with MySQL 3.23. Any ideas of how to accomplish this?