SQL select previous record where

So I am trying to query through a table, call it students if the column "status" in Student is 'active', then select the "firstName" and "lastName" coloumns from the previous record. Each record has a primary key. That is, if the "status" column of record is =active, 开发者_JAVA技巧then, i want to select the previos record. The table is already populated with data like

id  firstname   lastname    status          email
9   Joe         Peters      inactive        fp@gmail.com
10  kim         Rol         active          rt@yh.com
11  Lance       Ree         inactive        lll&hh.com
12  diana       Jones       active          sams@yahoo.com

For this data,the query should return Joe peters and lance ree How can i do this? thanks

If you simply want to select name from the records where the student is active, the query is a simple

SELECT id, firstname, lastname
FROM students
WHERE status = "active"

I assume what you mean by previous row is the row you just determined was an active student. The query will check your table for active students and retrieve their names. Because names are very rarely unique, I included the ID column. I also recommend setting the status column to a 0 or 1 integer.

If you really want to select the previous row, you can use

SELECT sj.id, sj.firstname, sj.lastname
FROM students AS s
LEFT JOIN students AS sj ON (sj.id = s.id - 1)
WHERE s.status = "active"

The point is in determining the relation between the rows you have and the rows you want to select. In this case, you want to select the row which has the ID 1 less than your current row. Since this constraint is very restrictive, it's not a good idea to select rows this way. You might want to redesign your database.

With previous record you probably mean the priviously inserted one. you can refer to that row using last_insert_id() - which is a mysql-function - that will return the last inserted auto-incremented value.

select * from T where id = last_insert_id();