CODEWITHSUNDEEP
phpmysqlsqltimestamp
Christian Burgos
Christian Burgos

Reputation: 1591

selecting from table where timestamp is latest

i have made a table for an audit trail.

CREATE TABLE IF NOT EXISTS `audit_trail_timer` (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `remaining_duration` varchar(50) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`_id`)
)

now i wanted to select a row where the timestamp is the most recent one.

$query = select * from audit_trail_timer where user_id='$id' and timestamp='';

Upvotes: 0

Views: 68

Answers (4)

sukhi
sukhi

Reputation: 924

SELECT top 1 * FROM audit_trail_timer WHERE user_id='$id' ORDER BY TIMESTAMP DESC;

Upvotes: 3

Bartek
Bartek

Reputation: 1359

Use

SELECT * 
FROM audit_trail_timer 
WHERE user_id = '$id'
ORDER BY `timestamp` DESC
LIMIT 1

timestamp is reserved keyword

Upvotes: 1

Ezhil
Ezhil

Reputation: 1006

   try this

   SELECT * FROM audit_trail_timer WHERE user_id='$id' 
    ORDER BY TIMESTAMP  DESC LIMIT 1

Upvotes: 1

ravikumar
ravikumar

Reputation: 893

try this,

  SELECT * FROM audit_trail_timer WHERE user_id='$id' 
  ORDER BY   TIMESTAMP DESC LIMIT 1

Upvotes: 1

Related Questions