Pre Exam Test Practice
Ask a Question
Advertise on boostr.in
Organizational Research By
Surprising Reserch Topic
getting last record from mysql
getting last record from mysql ¬†using -'java,mysql,sql,last-insert-id'
I am using mysql and facing some problem. I want to retrieve last row that is inserted.
<< Below are details >>
Below is how I created table.
create table maxID (myID varchar(4))
I inserted four values in it as below
insert into maxID values ('A001')
insert into maxID values ('A002')
insert into maxID values ('A004')
insert into maxID values ('A003')
When I execute select myID, last_insert_id() as NewID from maxID, I get output as below
A001 ¬†¬†0 ¬†
A002 ¬†¬†0 ¬†
A004 ¬†¬†0 ¬†
A003 ¬†¬†0 ¬†¬†¬†
When I tried below code,
select myId, last_insert_id() as NewID, @rowid:=@rowid+1 as myrow from maxID, (SELECT @rowid:=0) as init
I get output as below.
myId NewID ¬†rowid
A001 ¬†¬†0 ¬†¬†¬†¬†¬†1
A002 ¬†¬†0 ¬†¬†¬†¬†¬†2
A004 ¬†¬†0 ¬†¬†¬†¬†¬†3
A003 ¬†¬†0 ¬†¬†¬†¬†¬†4
However when I use code select myId, last_insert_id() as NewID, @rowid:=@rowid+1 as myrow ¬†from maxID, (SELECT @rowid:=0) as init where @rowid = 4, I get error as Uknown column 'myrow' in where clause
When I use where @rowid=4, I don't get any data in tables.
Link to play with data
Note: Here I am using 4 just to get desired output. Later I can get this from a query (select max(rowid) from maxID)
Please suggest me what need to do if I want to see only last record i.e. A003.
Thanks for your time.
I already have millions of data in my table so I can't add new column in it as suggested below.
Oct 11, 2015
to add a comment.
Related Hot Questions
Government Jobs Opening