CODEWITHSUNDEEP
databasemysql
MarkL
MarkL

Reputation: 3

selecting previous and next rows in mysql - how?

I can't figure out how to select a previous/next row IF the current row does not have any numeric identifiers.

With numeric value I always use 2 queries:

  SELECT min(customer_id) 
    FROM customers 
   WHERE `customer_id` < 10 
GROUP BY customer_status 
ORDER BY customer_name ASC 
  LIMIT 1;

  SELECT max(customer_id) 
    FROM customers 
   WHERE `customer_id` > 10 
GROUP BY customer_status 
ORDER BY customer_name DESC 
   LIMIT 1;

However, I don't have "customer_id" anymore and only "customer_name". When I query the DB and sort by this column, I get:

Ab
Bb
Cc
Dd
Ee

Let's assume my current customer's name is "Cc". I want to be able to select "Bb" and "Dd" from the DB. How? :)

Upvotes: 0

Views: 458

Answers (2)

Brian Hooper
Brian Hooper

Reputation: 22054

i'd select the previous one with...

  SELECT MAX(customer_name) 
    FROM customers 
   WHERE `customer_name` < 'Cc' 
  LIMIT 1;

and the next one with...

  SELECT MIN(customer_name) 
    FROM customers 
   WHERE `customer_name` > 'Cc' 
   LIMIT 1;

You where nearly there, I think.

Edit: Removed superfluous ORDER BY statements as suggested by Col. Shrapnel.

Upvotes: 0

Josh Ribakoff
Josh Ribakoff

Reputation: 3048

Rows do not have an order, mysql stores the rows in whatever order it wants. Its called clustering. You use LIMIT to grab subsets of a result set. LIMIT 10 says rows 1 to 10. LIMIT 11,20 says rows 11 to 20 and so on. Row 1 corresponding to the order of the row in the result set, since the rows in the tables are more like a "cloud", there is no order until you build a result set with an ORDER BY clause.

Upvotes: 1

Related Questions