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,Extra
ContentID,int(3),,PRI,NULL,auto_increment
Name,varchar(50),,,,
Filename,varchar(100),,,,
Thumbnail,varchar(100),,,,
FileTypeID,int(11),,,0,
UploadDate,datetime,,,0000-00-00 00:00:00,
Description,text,,,,
ContributorID,int(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]

Randy Jackson wrote:

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;

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]

Rick Lones <rl****@charter.net> wrote in news:10jn9hltio76hc7
@corp.supernews.com:

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-

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