CODEWITHSUNDEEP
mysqlsqlmysql-5.7
Carol.Kar
Carol.Kar

Reputation: 5355

Getting last entry from table

I am using mysql 5.7.32-1 and want to get the lastest row from my asset_profiles table.

CREATE TABLE `companies` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `symbol` varchar(191) NOT NULL,
  `name` varchar(191) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
);

CREATE TABLE `asset_profiles` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `companies_id` int(11) NOT NULL DEFAULT '999999',
  `persons_id` int(11) DEFAULT NULL,
  `address1` varchar(191) DEFAULT NULL,
  `address2` varchar(191) DEFAULT NULL,
  `city` varchar(191) DEFAULT NULL,
  `state` varchar(191) DEFAULT NULL,
  `compensation` bigint(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
);

ALTER TABLE `companies`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `asset_profiles`
  ADD PRIMARY KEY (`id`);

INSERT INTO `companies` (`id`, `symbol`, `name`, `created_at`, `updated_at`) VALUES
(1, 'AAPL', 'Apple Inc.', '2020-10-06 19:00:05', '2021-01-31 14:00:34'),
(2, 'AMZN', 'Amazon.com Inc.', '2020-10-06 19:00:05', '2021-01-31 14:00:38'),
(3, 'IBM', 'International Business Machines Corporation', '2020-10-06 19:00:05', '2021-01-31 14:00:40');


INSERT INTO `asset_profiles` (`id`, `companies_id`, `persons_id`, `address1`, `address2`, `city`, `state`, `compensation`, `created_at`, `updated_at`) VALUES
(1, 1, NULL, 'One Apple Park Way', NULL, 'Cupertino', 'CA', 13213124, '2021-01-08 00:08:33', '2021-01-08 00:08:33'),
(2, 1, NULL, 'One Apple Park Way', NULL, 'Cupertino', 'CA', 3323124, '2021-01-04 00:08:33', '2021-01-04 00:08:33'),
(3, 1, NULL, 'One Apple Park Way', NULL, 'Cupertino', 'CA', 32131, '2020-12-04 00:08:33', '2020-12-04 00:08:33'),
(4, 1, NULL, 'One Apple Park Way', NULL, 'Cupertino', 'CA', 132323124, '2020-11-06 00:08:33', '2020-11-06 00:08:33'),
(5, 1, NULL, 'One Apple Park Way', NULL, 'Cupertino', 'CA', 13224, '2020-10-08 00:08:33', '2020-10-08 00:08:33'),
(6, 2, NULL, 'Amz', NULL, 'Cupertino', 'CA', 1333231, '2020-12-04 00:08:33', '2020-12-04 00:08:33'),
(7, 2, NULL, 'Amz', NULL, 'Cupertino', 'CA', 132323323124, '2020-11-06 00:08:33', '2020-11-06 00:08:33'),
(8, 3, NULL, 'One Apple Park Way', NULL, 'Cupertino', 'CA', 13224, '2020-10-08 00:08:33', '2020-10-08 00:08:33');

I tried:

SELECT
    *
FROM
    companies c
inner join (
    select companies_id, max(created_at) as MaxDate
    from asset_profiles a
    group by a.companies_id
) tm on c.id = tm.companies_id and c.created_at = tm.MaxDate

I would like to get the following result:

| id  | symbol | name                                        | created_at          | updated_at          | id  | companies_id | persons_id | address1           | address2 | city      | state | compensation | created_at          | updated_at          |
| --- | ------ | ------------------------------------------- | ------------------- | ------------------- | --- | ------------ | ---------- | ------------------ | -------- | --------- | ----- | ------------ | ------------------- | ------------------- |
| 1   | AAPL   | Apple Inc.                                  | 2020-10-06 19:00:05 | 2021-01-31 14:00:34 | 1   | 1            |            | One Apple Park Way |          | Cupertino | CA    | 13213124     | 2021-01-08 00:08:33 | 2021-01-08 00:08:33 |
| 2   | AMZN   | Amazon.com Inc.                             | 2020-10-06 19:00:05 | 2021-01-31 14:00:38 | 6   | 2            |            | Amz                |          | Cupertino | CA    | 1333231      | 2020-12-04 00:08:33 | 2020-12-04 00:08:33 |
| 3   | IBM    | International Business Machines Corporation | 2020-10-06 19:00:05 | 2021-01-31 14:00:40 | 8   | 3            |            | One Apple Park Way |          | Cupertino | CA    | 13224        | 2020-10-08 00:08:33 | 2020-10-08 00:08:33 |

Find below my dbfiddle:

https://www.db-fiddle.com/f/oKeCF9gmpoQMdVm48NBXEQ/3

Any suggestions what I am doing wrong?

I appreciate your replies!

Upvotes: 1

Views: 36

Answers (2)

Popeye
Popeye

Reputation: 35900

You can use corelated subquery using not exists for it as follows:

select c.*, ap.*
from companies c 
join asset_profiles ap on c.id = tm.companies_id
where not exists 
      (Select 1 from asset_profiles app
        Where app.companies_id = ap.companies_id
          And app.created_at > ap.created_at)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use window functions in MySQL 8+:

select c.*, ap.*
from companies c join
     (select ap.*,
             row_number() over (partition by ap.companies_id order by ap.created_at desc) as seqnum
      from asset_profiles ap
     ) ap
     on c.id = tm.companies_id and seqnum = 1;

In earlier versions, I would suggest a correlated subquery:

select c.*, ap.*
from companies c join
     asset_profiles ap
     on c.id = tm.companies_id
where ap.created_at = (select max(ap2.created_at)
                       from asset_profiles ap2
                       where ap2.companies_id = ap.companies_id
                      );

For performance, I would recommend an index on asset_profiles(companies_id, created_at).

Upvotes: 1

Related Questions