How to min and max with SELECT DISTINCT-CodePudding

I have a table let's say abc that have entries with timestamps like

ID  timestamp   Name
1   2022-03-12  Jhon
2   2022-03-11  James
1   2022-03-20  Jhon

I want to create a new table using this table with DISTINCT values but also want to add 2 new columns first_seen and last_seen

CREATE TABLE xyz as 
SELECT DISTINCT ID,
              NAME, 
              min(timestamp) as first_seen, 
              max(timestamp) last_seen 
from abc

I am new to SQL and I can't understand the logic for this thing

I tried with different select combinations but all of them seems like hacks, I want to learn properlly about this thing as it can help me in future

CodePudding user response：

It seems you need a simple GROUP BY statement -

CREATE TABLE xyz as 
SELECT ID,
       NAME, 
       MIN(timestamp) as first_seen, 
       MAX(timestamp) last_seen 
  FROM abc
 GROUP BY ID,
          NAME;

CodePudding user response：

Maybe it is better creating a VIEW rather than a new table:

CREATE view xyz as 
SELECT        id,
              name, 
              min(timestamp) as first_seen, 
              max(timestamp) as last_seen 
from abc
group by id,name;

Demo

CodePudding user response：

The GROUP BY clause is used to collect data from multiple records and group the result by one or more columns. It provides various aggregations like COUNT, SUM, MIN, MAX, AVG, etc. on the grouped column.

CREATE TABLE IF NOT EXISTS ABC (
  ID int unsigned not null,
  NAME varchar(20) not null,
  TIMESTAMP datetime not null
);

INSERT INTO ABC (ID, NAME, TIMESTAMP)
VALUES (1, 'Anne', '2022-03-01'),
(2, 'Jane', '2022-04-01'),
(1, 'Anne', '2022-01-01'),
(3, 'Bob', '2021-12-01'),
(4, 'Alice', '2021-08-23'),
(3, 'Bob', '2021-06-12'),
(2, 'Jane', '2021-05-15'),
(3, 'Bob', '2021-03-21'),
(1, 'Anne', '2021-12-18');

CREATE TABLE xyz as 
SELECT ID, NAME, MIN(TIMESTAMP) AS first_seen, MAX(TIMESTAMP) AS last_seen
FROM ABC 
GROUP BY ID, NAME;

Working SQL fiddle: http://sqlfiddle.com/#!9/922afb/1/0

More about group by clause: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html