MySQL next row in sequence

Question

I have a query like this:

SELECT id, name, town, street, number, number_addition, telephone
FROM clients
WHERE ((postalcode >= 'AABB' AND postalcode <= 'AACC') OR (postalcode >= 'DDEE' AND postalcode <= 'DDFF'))
ORDER BY town ASC, street ASC, number ASC, number_addition ASC
LIMIT 1

This way I can get first client.

Then I want to get next client (let's say I know that my current client has ID 58 and I want to get next client in the sequence - I'll have one client that's tagged as current and I want to get next/previous) and I already know ID of first client, can you please give me a hint how to achieve this? (With the same ordering)

I found this http://www.artfulsoftware.com/infotree/queries.php#75 but I dont know how to transform these examples to the command when I need to order by multiple columns.

Thanks for your time.

can't you make LIMIT 3 and display the one in the middle?

mkk
– mkk

2011-09-23 14:37:22 +00:00
Commented Sep 23, 2011 at 14:37 — mkk
– mkk, Commented Sep 23, 2011 at 14:37

MPelletier · Accepted Answer · 2011-09-23 14:39:42Z

1

You can use the two values of LIMIT X,Y, where X is the offset and Y is the number of rows. See this for info.

That will, however, make your list order every time you query for just one row. There are different ways to do this.

What you could do is get a good portion of that list, maybe as much as is practical for you (say maybe 20, it depends). Keep the result in an array in your program, and itterate through those. If you need more, just query again with an offset. The same way a forum will show only a certain quantity of posts in each page on a search.

Another approach is to get all the client IDs you need, keep that in an array, and query for each one at a time.

edited Sep 23, 2011 at 14:39

answered Sep 23, 2011 at 14:28

MPelletier

16.8k18 gold badges90 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Johan Over a year ago

That's a slow solution, as offset increases, limit gets slower.

very sad mysql developer Over a year ago

Yes, I also considered that before but the problem is I'll hav to also remember last viewed client by the user and somehow apply his ID to the query. That means: SELECT next FROM table WHERE (ORDERING STUFF) AND CURRENT = 9876

MPelletier Over a year ago

@very: No, if you keep the list of IDs in reference, then you just need to remember the last ID viewed, find its position in the list, take the one after, and lookup its information, no sorting.

Johan · Accepted Answer · 2011-09-23 15:01:39Z

0

SELECT 
  c1.id, c1.name, c1.town, c1.street, c1.number, c1.number_addition, c1.telephone
FROM clients c1
INNER JOIN clients c2
        ON (c1.town,c1.street,c1.number,c1.number_addition) > 
           (c2.town,c2.street,c2.number,c2.number_addition)
WHERE   ((c1.postalcode BETWEEN 'AABB' AND 'AACC') 
      OR (c1.postalcode BETWEEN 'DDEE' AND 'DDFF'))
   AND c2.id = '$previous_id'
ORDER BY c1.town, c1.street, c1.number, c1.number_addition
LIMIT 1 -- OFFSET 0

Here it is assumed that id is the primary key for this table.
You need to add the id to the select list, or you will not know the id to put in $previous_id for the next one in line.

edited Sep 23, 2011 at 15:01

answered Sep 23, 2011 at 14:35

Johan

77.4k28 gold badges204 silver badges346 bronze badges

5 Comments

Johan Over a year ago

Dear drive-by-downvoter, please be so kind as to inform me of the cause of your displeasure with this answer?

very sad mysql developer Over a year ago

I'm not sure if I described the problem well. Let's say I'm in the middle of client listing - current client has ID eg. 1234 and I want to get next client in sequence (with ordering I described in example SQL) and next client can have higher / lower ID (ordering is based on client's address). If I use LIMIT or OFFSET to do this I will have to somehow know on which position in the table I'm currently on - I'll know just ID. To be clear, I'm not the "drive-by-downvoter" I really appreciate your help.

Johan Over a year ago

@verysadmysqldeveloper, Aha, now I understand. Note that the (x,y) > (x1,y1) is shorthand for ((x> x1) OR (x=x1 AND (y> y1))

very sad mysql developer Over a year ago

Thanks! That's a lot better. But If I inderstand it right if two clients are from same city, street, have same house number and only number_addition differs them it won't this client becase there's operator >?

Johan Over a year ago

If everything is the same except for the number_addition this will work correctly. Like I said, the (a,b,c,d,e,f) > (a1,b1,c1,d1,e1,f1) is shorthand for a long list of a>a1 OR (a=a1 AND b> b1) OR (a=a1 AND a=b1 AND c>c1) or (.....)

Collectives™ on Stack Overflow

MySQL next row in sequence

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related