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. 2 2535
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-
Rick Lones <rl****@charter .net> wrote in news:10jn9hltio 76hc7
@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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Roar |
last post by:
Hi!
I have got 1 access 2000 DB, one simple search form, and 3 .asp pages (one
for deleting a record, one for inserting a record and one for listing
searchresults). Deleting records works fine, Inserting records works fine,
but my headache is the searchform.. Whatever i type in the searchbox (sok),
the result is all database entries, not...
|
by: Randy Jackson |
last post by:
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...
|
by: Prasad Karunakaran |
last post by:
I am using the C# DirectoryEntry class to retrieve the Properties of
an user object in the Active Directory. I need to get the First Name
and Last Name as properties. I know it is not supported with the ADSI
NT Provider and only supported in the LDAP Provider.
So given an UserId (UID) how can I read the First Name and Last Name
using LDAP...
|
by: VMI |
last post by:
How can I get the bottom N records from an Access table and store them in my
DataTable?
For example, in my Access table with 2000 records, if I want to display
records 151-200 (with ID as PK), my query
would be:
"select top 50 * from audit where ID > 150 order by ID asc"
Since ID is type AutoNumber, I'll see records 151-200. But once I'm...
|
by: Roy Gourgi |
last post by:
Hi,
I am used to working in Visual FoxPro and I would like to be able to create
a database and store and retrieve information from it. What is the simplest
way to do it and what should I be using as there are many choices to choose
from.
My database will contain a lot of records.
TIA
| |
by: sck10 |
last post by:
Hello,
I have a method in my codefile that builds a sorted list (see CodeFile). I
am trying to create a class that does the same thing (see App_Code).
CodeFile
===================================
public void PostSearch(string HRID)
{
SortedList PostSearchList = new SortedList();
|
by: kev |
last post by:
Hi all,
I have created a database for equipments. I have a form to register the
equipment meaning filling in all the particulars (ID, serial, type,
location etc). I have two buttons at the end of the form which is
submit and cancel. After i have clicked submit, the information is
stored directly into my corresponding database table. My...
|
by: John Fairhurst |
last post by:
Hi,
The following code should select the specified number of records randomly
from the database
<%
....
query = "SELECT FROM "
Set RS = Server.CreateObject("ADODB.Recordset")
|
by: LetMeDoIt |
last post by:
Greetings,
I'm using ASP to retrieve from MSSQL and I then populate a table.
After several months of successfull retrieves, this same code now
bombs out. It turns out that if I clear out from SQL about 10,000
records, the retrieve is then successfull.
Reading some of the posts, I believe I need to set the cache. If
anyone can point out...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |