CODEWITHSUNDEEP
androidsqlsqliteandroid-sql
Lalit Jadiya
Lalit Jadiya

Reputation: 213

SQL Query for timestamp

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.

Upvotes: 1

Views: 103

Answers (4)

Vaibhav Barad
Vaibhav Barad

Reputation: 625

Try the following.

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

Upvotes: 2

Rahul
Rahul

Reputation: 77876

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;

Upvotes: 1

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 0

Payer Ahammed
Payer Ahammed

Reputation: 907

Try this:

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

Upvotes: 0

Related Questions