CODEWITHSUNDEEP
phpmysqlsql
Phil
Phil

Reputation: 14641

MySQL: Get the ID of the preceding record

I have a table:

ID  NAME    ...n
1   JANE
2   SAM
3   PENNY
4   WATSON
5   ALISON
6   HANNA
7   BRIAN
..  ..
.   .

This table is not ordered alphabetically. I want to make such a selection that:

1) The table gets alphabetically ordered A..Z (ORDER BY NAME)

2) I want to supply the ID (or NAME) and get the ID (or NAME) of the preceding row. e.g. -> Once alphabetically ordered. I want to supply BRIAN (7) and get ALISON (5)

I am using PHP and MySQL.

How can I achieve this?

Upvotes: 2

Views: 170

Answers (4)

adrien54
adrien54

Reputation: 1640

SELECT id, name
FROM names
WHERE name < 'BRIAN'
ORDER BY name DESC
LIMIT 1

Upvotes: 2

newtover
newtover

Reputation: 32094

Populate the @given_id with the required id and make sure you have an index on NAME:

SELECT @target_id as prev_id
FROM names, (SELECT @prev_id:=-1, @target_id:=-1, @given_id:=7) as init
WHERE IF(ID=@given_id,
         GREATEST(@target_id:=@prev_id, @prev_id:=ID),
         LEAST(0, @prev_id:=ID))
ORDER BY NAME;

If you need to fetch other fields beside id, put the query in a subquery and make a join with the table on prev_id = id.

Upvotes: 1

Stewie
Stewie

Reputation: 3121

Select * from table where id < (select id where condition order by condition) 
order by condition limit 1

Upvotes: 1

user359040
user359040

Reputation:

Try:

select ID, name from
(select *
 from myTable
 where name < ?
 order by name desc) sq
group by ()

Upvotes: 1

Related Questions