SQL Query for timestamp

Question

I had following Table

CREATE TABLE Customer
    ( `Name` varchar(7), `Address` varchar(55), `City` varchar(15),`Contact` int,`timestamp` int)
;

INSERT INTO Customer
    (`Name`,`Address`, `City`, `Contact`,`timestamp`)
VALUES
    ('Jack','New City','LA',79878458,456125),
    ('Joseph','New Lane23','LA',87458458,794865),
   ('Rosy','Old City','Paris',79878458,215125),
   ('Maria','New City','LA',79878458,699125),
   ('Jack','New City','LA',79878458,456125),
   ('Rosy','Old City','Paris',79878458,845125),
   ('Jack','New Main Street','New York',79878458,555525),
   ('Joseph','Near Bank','SAn Francisco',79878458,984521)

;

I want to get all customer record with highest timestamp without duplication.

score 2 · Answer 1 · answered Sep 14 '15 at 05:01

2

Try the following.

select name,max(timestamp),Address,City,Contact from Customer group by name

answered Sep 14 '15 at 05:01

Vaibhav Barad

625
8
17

Rahul · Accepted Answer · 2015-09-13T17:52:30.913

1

I want to get all customer record with highest timestamp without duplication.

Use DISTINCT operator and ORDER BY clause like

select distinct `Name`,`Address`, `City`, `Contact`,`timestamp`
from customer
order by `timestamp` desc;

In that case you can use JOIN query like

select t1.*
from customer t1 join
(select Name, max(`timestamp`) as maxstamp
 from customer
 group by Name) xx 
 on t1.Name = xx.Name
 and t1.`timestamp` = xx.maxstamp;

edited Sep 13 '15 at 17:52

answered Sep 13 '15 at 17:45

Rahul

76,197
13
71
125

But want to show record with same name having highest timestamp – Lalit Jadiya Sep 13 '15 at 17:47

score 0 · Answer 3 · answered Sep 13 '15 at 17:40

0

Try this:

SELECT * FROM `customer` 
group by name,Address,City,Contact,timestamp
order by timestamp desc

answered Sep 13 '15 at 17:40

Payer Ahammed

887
1
5
16

But want to show record with same name having highest timestamp – Lalit Jadiya Sep 13 '15 at 17:45
if your databse is mysql you should try this: "SELECT Name,max(timestamp) as timestamp FROM `customer` group by Name order by Name asc,timestamp asc" – Payer Ahammed Sep 13 '15 at 17:53

score 0 · Answer 4 · answered Sep 13 '15 at 17:46

0

I'm joining the Customer table with itself, the condition c1.timestamp<c2.timestamp on the join clause combined with c2.timestamp IS NULL will make sure that only the latest record for each person is returned. I put DISTINCT because on your sample data there are two records for Jack with the same timestamp:

SELECT DISTINCT
  c1.*
FROM
  Customer c1 LEFT JOIN Customer c2
  ON c1.Name=c2.Name
     AND c1.Contact=c2.Contact -- you might want to remove this
     AND c1.timestamp<c2.timestamp
WHERE
  c2.timestamp IS NULL
ORDER BY
  Name, Address

Please see a fiddle here.

answered Sep 13 '15 at 17:46

fthiella

48,073
15
90
106

select t1.* from customer t1 join (select Name, max(`timestamp`) as maxstamp from customer group by Name) xx on t1.Name = xx.Name and t1.`timestamp` = xx.maxstamp; – Lalit Jadiya Oct 07 '15 at 11:45
But if any rows is repeated/duplicate it also show that row repeatedly – Lalit Jadiya Oct 07 '15 at 12:07
@LalitJadiya using a subquery is an alternative solution, but I prefer using a LEFT JOIN ... yes, if you have two contacts with different details but same max timestamp they will be duplicated... but which record do you want to return in that case? – fthiella Oct 07 '15 at 12:34

SQL Query for timestamp

4 Answers4