0

Suppose I had a table with 5 fields or so and I wanted the next record but I want it to be the next one as if the records were ordered by 3 of the fields, as seen in the MySQL statement below: 

SELECT id FROM t1 ORDERBY f1 ASC, f2 ASC, f3 ASC;

Is there a way to do this?

Manny Fleurmond
  • 362
  • 1
  • 6
  • 18
  • What do you mean, "next record"? If you're doing a select, just do another fetch call and you'd get the next record. – Marc B Aug 12 '11 at 16:17
  • @DeviantSeev Basically, I have a table with 100 or so records. When I list the records on a web page, I have them sorted as I have in the MySql call I have listed above: they are sorted via 3 of the fields. I want it so if I am on an individual page for one of the records, that it links to the next one in the sorted list. – Manny Fleurmond Aug 12 '11 at 16:20
  • @Manny: use a `limit` clause, and keep track of row numbers (not ids) so you can easily change your limit offsets to move forward/backwards in the query. – Marc B Aug 12 '11 at 16:21
  • @Marc B could you give me an example?? – Manny Fleurmond Aug 12 '11 at 16:26
  • @Manny: http://stackoverflow.com/questions/5489988/pagination-in-php – Marc B Aug 12 '11 at 16:29
  • Is there a way of doing it strictly in MySQL? – Manny Fleurmond Aug 12 '11 at 16:30
  • 2
    @Manny the problem is that MySQL just returns the data based on your query. One thing you can do is write a stored procedure which takes in a row number as a parameter and returns the data based on that number. You can read about stored procedures here: http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx Then you can combine that stored procedure with the example which Marc gave you. – evasilchenko Aug 12 '11 at 17:40

1 Answers1

0

Found a solution: By using Concat_ws of the two fields in both the WHERE (to select the next one) and the ORDER BY Clauses.

Manny Fleurmond
  • 362
  • 1
  • 6
  • 18