Mysql Select multiple columns and group by single column

Question

I have a table in mysql where i need to display the columns Uid, CreatedDate and Action_key and Value, grouping by action key.

Table: UsersOptStatus

Expected result:

I need to order by desc CreatedDate and show the latest Action_keys

Below is what i tried - This gives me all the Action_keys, but i need to show latest Action_keys based on last (ie Desc) Created_date:

SELECT uid,
       action_key,
       value,
       Max(createddate) CreatedDate
FROM   usersoptstatus
WHERE  uid = 1607
GROUP  BY action_key,
          value;

Even the below query not gives me Expected Result,

SELECT uid,
       value,
       action_key
FROM   (SELECT uid,
               Max(createddate) CreatedDate
        FROM   usersoptstatus
        GROUP  BY uid) A
       INNER JOIN usersoptstatus USING (uid, createddate)
WHERE  uid = 1607
ORDER  BY id DESC;

No images pls. Always copy the tables as formatted code text. — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 9, 2018 at 7:43
Which version of MySQL? 8.0 introduces window functions, which would allow for a simpler, arguably easier solution. — GolezTrol
– GolezTrol, Commented Nov 9, 2018 at 7:47

Madhur Bhaiya · Accepted Answer · 2018-11-09 07:47:32Z

2

In the Derived table, you are doing Group By on uid. Instead, you need to Group By on action_key to get the maximum value of createddate for every action_key.

Now, you can join this result-set to the main table on action_key and createddate.

Also, I prefer ON clause based joining instead of either Using or old-school comma based implicit Joins.

SELECT uos.uid,
       uos.value,
       uos.action_key
FROM   (SELECT action_key,
               Max(createddate) AS maxcreateddate
        FROM   usersoptstatus 
        WHERE uid = 1607 
        GROUP  BY action_key) A
INNER JOIN usersoptstatus AS uos
  ON uos.action_key = A.action_key AND 
     uos.createddate = A.maxcreateddate
WHERE  uos.uid = 1607
ORDER  BY uos.id DESC;

answered Nov 9, 2018 at 7:47

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Madhur Bhaiya Over a year ago

@Sushivam are you using MySQL version 8.0+ ?

Sushivam Over a year ago

No this is 5.6!

Sushivam Over a year ago

hi @Madhur Bhaiya, bro , wanted to know like, if i select * from usersoptstatus, if the Action_key is loggedin and value = 0 (for some time xx) and again if i do the entry action_key = loggedin and value =0 (at time xy), i should not store that entry if do select * from table..how can i achieve this

Madhur Bhaiya Over a year ago

@Sushivam it is hard to understand what you are asking with just words. Seeing sample table data and expected output helps instead. Please try to put a question. This time don't use images. Please check this link: meta.stackoverflow.com/q/333952/2469308

Sushivam Over a year ago

Have updated my question here : stackoverflow.com/questions/53313411/…

Collectives™ on Stack Overflow

Mysql Select multiple columns and group by single column

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related