1

I have a windows function (over, partitioned by) in my code:

FROM   (SELECT wp_posts.id, 
                                     wp_postmeta.post_id, 
                                     post_title, 
                                     post_type, 
                                     meta_value, 
                                     Row_number() 
                                       OVER( 
                                         partition BY post_title 
                                         ORDER BY wp_postmeta.meta_value) rn

but apparently this isn't supported on MariaDB before 10.2 (-I am using 10.1). Could someone please suggest alternative code which is both efficient and works on MariaDB 10.1 also?

dbfiddle provided, unfortunately with only MariaDB 10.2 as the oldest; can't test 10.1 directly here

create table wp_posts (
  ID integer primary key auto_increment,
  post_title varchar(30),
  post_type varchar(30)
);

✓

create table wp_postmeta (
  ID integer primary key auto_increment,
  post_id integer,
  meta_key varchar(30) not null default '_regular_price',
  meta_value integer not null
);

✓

insert into wp_posts (post_title, post_type) values
('Apple Pie','Product'),
('French Toast','Product'),
('Shepards Pie','Product'),
('Jam Pie','Product'),
('Jam Pie','Product'),
('Plate','Not a Product'),
('Bucket','Not a Product'),
('Chequebook','Not a Product'),
('French Toast','Product'),
('French Toast','Product'),
('Banana','Product'),
('Banana','Product'),
('Banana','Product');

✓

insert into wp_postmeta (post_id, meta_value) values
(1,10),
(2,5),
(3,9),
(4,8),
(5,11),
(6,12),
(7,10),
(8,6),
(9,1),
(10,1),
(11,7),
(12,2),
(13,2);

✓

-- Deleting all duplicate products in wp_posts table 
DELETE FROM wp_posts 
WHERE  id IN (SELECT id 
              FROM   (SELECT id, 
                             post_title, 
                             post_type, 
                             meta_value 
                      FROM   (SELECT wp_posts.id, 
                                     wp_postmeta.post_id, 
                                     post_title, 
                                     post_type, 
                                     meta_value, 
                                     Row_number() 
                                       OVER( 
                                         partition BY post_title 
                                         ORDER BY wp_postmeta.meta_value) rn 
                              FROM   wp_postmeta 
                                     JOIN wp_posts 
                                       ON wp_postmeta.post_id = wp_posts.id 
                              WHERE  wp_posts.post_type = 'Product' 
                                     AND wp_postmeta.meta_key = '_regular_price' 
                             ) t 
                      WHERE  t.rn <> 1) AS aliasx); 

✓

db<>fiddle here

Community
  • 1
  • 1
ptrcao
  • 421
  • 1
  • 5
  • 19
  • Tell us the intent of the query. – Rick James Feb 07 '19 at 01:11
  • There are two tables `wp_posts` containing the `post_title` column and `wp_postmeta`, containing the `meta_value` column. Both tables have an integer incremented column (`wp_posts.ID`, `wp_postmeta.post_id`) which allows them to be linked/joined (1 matches with 1, 2 matches with 2, etc.). `post_title` and `meta_value` are not necessarily unique, however, I want to to make aggregate selections grouped by `post_title`. Always select the lowest `meta_value`, and then select the corresponding `wp_posts.ID` for that `meta_value` – ptrcao Feb 07 '19 at 01:20
  • Follow the [groupwise-maximum] tag for lots of tips on achieving that. – Rick James Feb 07 '19 at 01:26

0 Answers0