CODEWITHSUNDEEP
mysqlsql
jason
jason

Reputation: 3962

get latest entry even if 2 records have same timestamp

I am using the code below to retrieve the latest data w.r.t all users .But if the user had points added at the same time stamp then I would like to get the last entry not both like in the example below.How do I make sure that I get latest entry even if 2 records have same timestamp.

http://sqlfiddle.com/#!2/374db/1

I really appreciate any help.Thanks in Advance.

CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
sender varchar(255),
receiver varchar(255),
 msg varchar(255),
 date timestamp,
  points varchar(255),
  refno varchar(255),
 PRIMARY KEY (id)
);

CREATE TABLE if not exists tblB
(
id int(11) NOT NULL auto_increment ,
sno varchar(255),
name varchar(255),
 PRIMARY KEY (id)
);

CREATE TABLE if not exists tblC
(
id int(11) NOT NULL auto_increment ,
data varchar(255),
  refno varchar(255),
  extrarefno varchar(255),
 PRIMARY KEY (id)
);


INSERT INTO tblA (sender, receiver,msg,date,points,refno ) VALUES
('1', '2', 'buzz ...','2011-08-21 14:11:09','10','001'),
('1', '2', 'test ...','2011-08-21 14:12:19','20','002'),
('4', '2', 'test ...','2011-08-21 14:13:19','30','003'),
('1', '3', 'buzz ...','2011-08-21 14:11:09','10','004'),
('1', '3', 'test ...','2011-08-21 14:12:19','20','005'),
('1', '4', 'buzz ...','2011-08-21 14:11:09','10','006'),
('1', '4', 'test ...','2011-08-21 14:12:19','20','007'),
('3', '4', 'test ...','2011-08-21 14:13:19','20','008'),
('2', '4', 'test ...','2011-08-21 14:13:19','20','009');



INSERT INTO tblB (sno, name ) VALUES
('1', 'Aa'),
('2', 'Bb'),
('3', 'Cc'),
('4', 'Dd'),
('5', 'Ee'),
('6', 'Ff'),
('7', 'Gg'),
('8', 'Hh');


INSERT INTO tblC (data,refno,extrarefno ) VALUES
('data1', '001', '101'),
('data2', '002', '102'),
('data3', '003', '103'),
('data4', '004', '101'),
('data5', '005', '102'),
('data6', '006', '103'),
('data7', '007', '101'),
('data8', '008', '101'),
('data9', '009', '101');

/// query:

SELECT *
FROM (

  SELECT tblB.*, MAX(tblA.date) AS date
  FROM tblB
  JOIN tblA ON tblB.sno = tblA.receiver
  GROUP BY tblB.sno

) AS subset
JOIN tblA ON subset.sno = tblA.receiver 
            AND subset.date = tblA.date JOIN tblC ON tblA.refno=tblC.refno

Upvotes: 1

Views: 923

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

The key idea is to use the id column instead of the date column. It is auto-incremented, so the biggest id should be more recent.

However, your query has another problem which is the join to tblB in the subquery. Arbitrary ("indeterminate") values from tblB would be returned in the outer query. Instead, just aggregate on tblA and move the join to tblB to the outer level:

SELECT *
FROM (SELECT tblA.receiver, MAX(tblA.id) AS id
      FROM tblA
      GROUP BY  tblA.receiver
    ) subset JOIN
    tblA
    ON subset.receiver = tblA.receiver AND subset.id = tblA.id JOIN
    tblB
    on tblA.receiver = tblB.sno join
    tblC
    ON tblA.refno=tblC.refno ;

Upvotes: 1

Dan Power
Dan Power

Reputation: 1141

Order by the date AND the id. The id is set to auto increment, if the date is the same, you can assume the higher id was created after.

ORDER BY date, id

Upvotes: 0

Related Questions