[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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.
0
cybeh
Asked:
cybeh
1 Solution
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
fiboCommented:
What if you do NOT use LIMIT 1 and simply loop thru all the records, one at a time?
0
 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now