how to get alphabetically next and prev records wiht minimal fetched records??

Question

I have a page that is displaying a company name and its details from a table A. Now say i have a company displayed,and its name is 'Company_one' now i want to have alphabetically sorted next company and previous company and their details etc.

The data in my table is not sorted.Its stored as it gets the data.

So now what kind of query should i write that it gives only one previous and one next alphabetically sorted record?? Plz help!!

Answer 1

You need to use the sort clause to sort your table. The prototype for sort is:

sort by fieldname

Example Query:

select * from your_table sort by company asc

If you want to limit records, use limit clause:

select * from your_table sort by company asc limit 0, 1

Answer 2

There's no nice way to do that in a single query. Just do two queries.

To get the previous one:

SELECT * FROM companies
WHERE name < variable_with_current_name
ORDER BY name DESC
LIMIT 1

To get the next one along:

SELECT * FROM companies
WHERE name > variable_with_current_name
ORDER BY name ASC
LIMIT 1