getting last row

[Chiranjeevi Kanthraj]

hi
Can any one tell me query for getting last row from the table.
Thanks

[Paul Sturrock]

There are a variety of ways you can do this, all of which are DB dependent. Which database are you using?
[ July 18, 2008: Message edited by: Paul Sturrock ]

[Chiranjeevi Kanthraj]

MySQL

[Paul Sturrock]

If you are using InnoDB you will have a clustered index on your primary key column. The ordering of your clustered index will match the physical ordering of your data, so the maximum primary key will be the last row. Soemthing like:

select entity_id from entity group by entity_id having entity_id = (select max(entity_id) from entity)

will get you that.

[Chiranjeevi Kanthraj]

But my primary key is Date time Stamp i think this max wont work with date time stamp

[Paul Sturrock]

Originally posted by Chiru Raj:
But my primary key is Date time Stamp i think this max wont work with date time stamp

Why not? Max should returns the latest date; this is how it behaves on most DBs. Is MySQL different?

[Chiranjeevi Kanthraj]

ok thank you.
i will check that and tell you.

[Scott Selikoff]

Just wondering, but is the purpose of your query to retrieve a row id (or other information) immediately after it's inserted? If so there is a getGeneratedKeys() command that will return the primary key of any records created by a statement. Of course support for this feature is system dependent so test it out before assuming this works. The other approach to get the id of the keys recently inserted without relying on the database is to create the ids of the keys yourself (instead of using the database's key generation feature). In large enough systems where concurrency is an issue, this is often the best approach.
[ July 18, 2008: Message edited by: Scott Selikoff ]

[Campbell Ritchie]

I think in MySQL you use the last_insert_id function for what Scott suggested. Check the documentation.

[Campbell Ritchie]

. . . but last_insert_id only works where you have an auto_increment column.

[Chiranjeevi Kanthraj]

Originally posted by Paul Sturrock:

select entity_id from entity group by entity_id having entity_id = (select max(entity_id) from entity)

Error is coming

  You have an error in your SQL syntax.  Check the
 manual that corresponds to your MySQL server version for the right syntax 
to use near 'select max(datetime) from tablename)' at line 1  

my query is

  Select * from tablename group by datetime 
having datetime in(select max(datetime) from biopsycalc)  

[ July 19, 2008: Message edited by: Scott Selikoff ]

[Chiranjeevi Kanthraj]

This is working fine

  SELECT * FROM tablename ORDER BY datetime DESC LIMIT 1