SELECT rows based on ids in a view-CodePudding

I have this query :

SELECT `company_id`, `product_id`, `expiresAt`, `createdAt`, `createdBy`, `disabledAt`
FROM subscription
WHERE `disabledAt` IS NULL AND `company_id` IN(16867, 19164)
ORDER BY `createdAt` DESC;

Which produces this:

company_id  product_id  expiresAt   createdAt       createdBy   disabledAt  
19164   3   2023-05-02  2022-06-09 12:41:37 1   NULL    
19164   3   2022-05-02  2022-06-09 12:40:47 1   NULL    
16867   3   2023-05-31  2022-05-20 08:18:36 1   NULL    
16867   3   2023-05-31  2022-05-20 08:18:08 1   NULL    
16867   3   2022-05-31  2022-05-12 14:49:51 1   NULL    
16867   3   2022-05-26  2021-05-26 07:20:52 1   NULL

I want to select 1st instance on each company_id. For example:

19164 3 2023-05-02 2022-06-09 12:41:37 1 NULL

16867 3 2023-05-31 2022-05-20 08:18:36 1 NULL

How can I achieve this? Thanks

EDIT:

Applying group by gives wrong result:

SELECT `company_id`, `product_id`, `expiresAt`, `createdAt`, `createdBy`, `disabledAt`
FROM subscription
WHERE `disabledAt` IS NULL AND `company_id` IN(16867, 19164)
GROUP BY `company_id`
ORDER BY `createdAt` DESC;

Result:

company_id  product_id  expiresAt   createdAt       createdBy   disabledAt  
19164   3   2022-05-02  2022-06-09 12:40:47 1   NULL    
16867   3   2022-05-26  2021-05-26 07:20:52 1   NULL

MYSQL SERVER VERSION: 5.7.39

CodePudding user response：

Using ROW_NUMBER and PARTITION BY you can achieve this. Try below

select * from
(
SELECT company_id, 
product_id, 
expiresAt, 
createdAt, 
createdBy, 
disabledAt,
ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY createdAt desc) AS rownumber
FROM subscription
WHERE disabledAt IS NULL AND company_id IN(16867, 19164)
) t where rownumber=1

CodePudding user response：

SELECT 
`company_id`,
`product_id`, 
`expiresAt`,
`createdAt`, 
`createdBy`, 
`disabledAt`
FROM subscription
WHERE `disabledAt` IS NULL AND `company_id` IN(16867, 19164)
GROUP BY company_id
ORDER BY `createdAt` DESC;

CodePudding user response：

Create a temporary table with same columns but the company_id is unique.

CREATE TABLE `subscription_temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) DEFAULT NULL,
  `product_id` int(11) NOT NULL,
  `expiresAt` date NOT NULL,
  `createdAt` datetime NOT NULL,
  `createdBy` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `disabledAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_A3C664D3979B1AD6` (`company_id`),
  KEY `IDX_A3C664D34584665A` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Then we can insert the data into it like:

INSERT IGNORE INTO subscription_temp (`company_id`, `product_id`, `expiresAt`, `createdAt`, `createdBy`, `disabledAt`)
SELECT `company_id`, `product_id`, `expiresAt`, `createdAt`, `createdBy`, `disabledAt`
FROM subscription
WHERE `disabledAt` IS NULL AND `company_id` IN(SELECT DISTINCT `company_id` FROM subscription)
ORDER BY `createdAt` DESC;

With this it will only keep the first occurrence of row and ignore the rest hence having a data with desired result.

CodePudding user response：

 SELECT `company_id`, 
           `product_id`, 
           `expiresAt`, 
           `createdAt`, 
           `createdBy`, 
           `disabledAt`
    FROM subscription 
    group by company_id
having `company_id` IN(16867, 19164);