Geek asks and answers

How to make a group by clause with max

I have a request similar to thos:

SELECT max(insert_date),
       creative_id,
       creative_object
FROM   rtb_creatives
WHERE  adgroup_id = 'agid1608844879'
       AND is_delete IN ( 0 )
GROUP  BY insert_date,
          creative_id,
          creative_object

eg. I have 4 lines: insert_date creative_id, creative_object

 june 12 a b
 june13 a b
 june 12 c d
 june13 c d

The query returns all rows.

I need to return

june13 a b
 june13 c d

How do I change the request?

+5
source share
4 answers

Just remove insert_datefrom the sentence GROUP BY:

SELECT max(insert_date) AS insert_date, creative_id, creative_object
from rtb_creatives 
where adgroup_id='agid1608844879' and is_delete in (0)
group by creative_id, creative_object
+5
source 
create table rtb_creatives (insert_date varchar(20), creative_id char(1), creative_object char(1));
insert into rtb_creatives (insert_date, creative_id, creative_object) values
('june 12', 'a', 'b'),
('june 13', 'a', 'b'),
('june 12', 'c', 'd'),
('june 13', 'c', 'd')
;

SELECT insert_date, creative_id, creative_object 
from rtb_creatives 
where 
    adgroup_id='agid1608844879' 
    and is_delete in (0)
    and insert_date = (select max(insert_date) from rtb_creatives)
group by insert_date, creative_id, creative_object
;
+-------------+-------------+-----------------+
| insert_date | creative_id | creative_object |
+-------------+-------------+-----------------+
| june 13     | a           | b               |
| june 13     | c           | d               |
+-------------+-------------+-----------------+
0
source

, creative_id creative_object, insert_date GROUP BY, , IN, ( ), :

SELECT max(insert_date) as insert_date,
       creative_id,
       creative_object
FROM   rtb_creatives
WHERE  adgroup_id = 'agid1608844879'
       AND is_delete =  0
GROUP  BY creative_id,
          creative_object
0

The problem is that the insert date is defined as a string. It needs to be defined as a date type so that Max () can calculate the correct order. Suppose there is another date, such as August 05th, as the insertion date, then Max will return on June 13th. And keep in mind that in the order of the rows there is a difference on June 13th and June 13th.

When you max (expression), the expression should not be in the group by clause.

GROUP BY insert_date, creative_id, creative_object

Hope this helps.

0
source

More articles:


All Articles