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?
 
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)
0
 
todd_farmerCommented:
SELECT * FROM tblTmp ORDER BY nDate, nPoints DESC, nPrimaryID LIMIT 1,1;

0
 
todd_farmerCommented:
For the record after that:

SELECT * FROM tblTmp ORDER BY nDate, nPoints DESC, nPrimaryID LIMIT 2, 1;
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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.
0
 
star_trekCommented:
try this
select id, max(Date),Points from tblTmp group by id;
0
 
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.
0
 
Bernard S.CTOCommented:
What if you do NOT use LIMIT 1 and simply loop thru all the records, one at a time?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.