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

Hi,

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.
LVL 1
cybehAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

todd_farmerCommented:
For the record after that:

SELECT * FROM tblTmp ORDER BY nDate, nPoints DESC, nPrimaryID LIMIT 2, 1;
cybehAuthor Commented:
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

star_trekCommented:
try this
select id, max(Date),Points from tblTmp group by id;
todd_farmerCommented:
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.
Bernard S.CTOCommented:
What if you do NOT use LIMIT 1 and simply loop thru all the records, one at a time?
NovaDenizenCommented:
SELECT * FROM tblTmp
    WHERE
        (nDate > $lastDate)
      OR
        (nDate = $lastDate AND nPoints < $lastPoints)
      OR
        (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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.