SQL statement needed (get the next record while the data return are not sorted via unique filed)

Posted on 2006-04-14
Last Modified: 2012-06-27

I do have a table contain nDate, nPoints, nDescription, nPrimaryID

during SQL statement, I have "SELECT * FROM tblTmp ORDER BY nDate, nPoints DESC, nPrimaryID LIMIT 1"
so, it will return 1 records. If I would like to navigate to the next record, what should i do?

I the past, my data are sorted via PrimaryID, so, I can have something like this
"SELECT * FROM tblTemp WHERE nPrimaryID > " & avalue & " ORDER BY nDate, nPoints DESC, nPrimaryID LIMIT 1"

I can't make a "AND" for >= Date, <= Points and > PrimaryID, cus if I have a sample data like below

PrimaryID             Date               Points
1                         4/12                50
3                         4/13                60
2                         4/13                50
4                         4/13                20
5                         4/14                30

If I using AND for all 3 statement, while my current display is Record with PrimaryID 3, while I make a next, it will navigate to PrimaryID 4 and not 2.

Please advice.
Question by:cybeh
    LVL 30

    Expert Comment

    SELECT * FROM tblTmp ORDER BY nDate, nPoints DESC, nPrimaryID LIMIT 1,1;

    LVL 30

    Expert Comment

    For the record after that:

    SELECT * FROM tblTmp ORDER BY nDate, nPoints DESC, nPrimaryID LIMIT 2, 1;
    LVL 1

    Author Comment

    But the problem is, I might not know what is the numbering for current record.
    I might be a display data from another search, and while it click on next, it should be just go to the next record.
    LVL 11

    Expert Comment

    try this
    select id, max(Date),Points from tblTmp group by id;
    LVL 30

    Expert Comment

    You have to handle that at the application layer.  MySQL isn't going to cache a result set and know remember the state of the last requested row so that it can increment the row and return the next one at the next request.  I may be misunderstanding, but it sounds like this is what you are after.
    LVL 29

    Expert Comment

    What if you do NOT use LIMIT 1 and simply loop thru all the records, one at a time?
    LVL 22

    Accepted Solution

    SELECT * FROM tblTmp
            (nDate > $lastDate)
            (nDate = $lastDate AND nPoints < $lastPoints)
            (nDate = $lastDate AND nPoints = $lastPoints AND nPrimaryID > $lastPrimaryID)
    ORDER BY nDate, nPoints DESC, nPrimaryID LIMIT 1

    For this to work quickly, you need a multicolumn index on (nDate, nPoints, nPrimaryID)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now