1

I have following tables with data as:

1.Table follow_up as : 

mysql> select * from follow_up;
+--------------+----------------+--------------------------------------------------+-------------------+---------+---------------+-----------+---------------+----------+
| follow_up_id | feedback_close | feedback_open                                    | is_email_required | is_Open | reminder_date | client_id | conclusion_id | stage_id |
+--------------+----------------+--------------------------------------------------+-------------------+---------+---------------+-----------+---------------+----------+
|            1 | NULL           | dsffsdfsdfsd                                     |                 1 |       1 | 2017-09-20    |       101 |            96 |       72 |
|            2 | NULL           | FSGDFHFGHFG                                      |                 1 |       1 | 2017-09-28    |       101 |           251 |       72 |
|            3 | NULL           | Tender stage fb                                  |                 0 |       1 | NULL          |       101 |            98 |      163 |
|            4 | NULL           | Call back tender stage update date from 28 to 30 |                 1 |       1 | 2017-09-28    |       101 |            96 |      163 |
|            5 | NULL           | Metting follow up for next meeting               |                 1 |       1 | 2017-10-02    |       101 |            96 |       73 |
+--------------+----------------+--------------------------------------------------+-------------------+---------+---------------+-----------+---------------+----------+

2. Table logs as : 

mysql> SELECT *  from logs where transaction = 'FLWUP';
+---------+---------+---------------------+---------+-------------+
| user_id | menu_id | logs_time           | tran_id | transaction |
+---------+---------+---------------------+---------+-------------+
|      84 |      69 | 2017-09-19 19:31:04 |       1 | FLWUP       |
|      84 |      69 | 2017-09-19 19:31:25 |       2 | FLWUP       |
|      84 |      69 | 2017-09-20 19:10:41 |       2 | FLWUP       |
|      84 |      69 | 2017-09-21 12:35:01 |       3 | FLWUP       |
|      84 |      69 | 2017-09-21 12:35:26 |       4 | FLWUP       |
|      84 |      69 | 2017-09-21 12:36:16 |       4 | FLWUP       |
|      84 |      69 | 2017-09-21 12:38:30 |       5 | FLWUP       |
+---------+---------+---------------------+---------+-------------+
7 rows in set (0.00 sec)

3. table allcode as :

mysql> select * from allcode where code_type like 'MARK%';
+------------------+---------+------+----------------------+
| code_type        | code_id | srno | code_name            |
+------------------+---------+------+----------------------+
| MARKETING_STAGES |      72 |    1 | Enquiry              |
| MARKETING_STAGES |      73 |    3 | Meeting              |
| MARKETING_STAGES |      74 |    4 | Presentation         |
| MARKETING_STAGES |     163 |    2 | Tender               |
+------------------+---------+------+----------------------+
11 rows in set (0.00 sec)

I have invoked a query and got result as :

mysql> select f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, 
ast.code_name as stage, ac.code_name as conclusion, max(l.logs_time)  
from follow_up f 
join logs l on l.tran_id = f.follow_up_id 
join allcode ast on ast.code_id = f.stage_id 
join allcode ac on ac.code_id = f.conclusion_id 
where l.transaction='FLWUP' and f.client_id = 101 
group by ast.code_name order by ast.srno;
+--------------+------------------------------------+----------------+---------------+---------+------------+---------------------+
| follow_up_id | feedback_open                      | feedback_close | reminder_date | stage   | conclusion | max(l.logs_time)    |
+--------------+------------------------------------+----------------+---------------+---------+------------+---------------------+
|            1 | dsffsdfsdfsd                       | NULL           | 2017-09-20    | Enquiry | Call Back  | 2017-09-20 19:10:41 |
|            3 | Tender stage fb                    | NULL           | NULL          | Tender  | Next       | 2017-09-21 12:36:16 |
|            5 | Metting follow up for next meeting | NULL           | 2017-10-02    | Meeting | Call Back  | 2017-09-21 12:38:30 |
+--------------+------------------------------------+----------------+---------------+---------+------------+---------------------+
3 rows in set (0.00 sec)

But I want result as : 

+--------------+-----------------------------------------------------+----------------+---------------+---------+------------+---------------------+
| follow_up_id | feedback_open                                       | feedback_close | reminder_date | stage   | conclusion | max(l.logs_time)    |
+--------------+-----------------------------------------------------+----------------+---------------+---------+------------+---------------------+
|            2 | FSGDFHFGHFG                                         | NULL           | 2017-09-20    | Enquiry | Call Back  | 2017-09-20 19:10:41 |
|            4 | Call back tender stage update date from 28 to 30    | NULL           | NULL          | Tender  | Next       | 2017-09-21 12:36:16 |
|            5 | Metting follow up for next meeting                  | NULL           | 2017-10-02    | Meeting | Call Back  | 2017-09-21 12:38:30 |
+--------------+-----------------------------------------------------+----------------+---------------+---------+------------+---------------------+
3 rows in set (0.00 sec)

I'm not able to JOIN and group by to get required result.

column conclusion_id and stage_id of table follow_up are referring to code_id of table allcode.

Question :
the result I want is to be

  1. group by stage_id,
  2. order by srno of allcode and
  3. last/recent follow_up_id of follow_up table
Shantaram Tupe
  • 1,646
  • 3
  • 16
  • 42
  • is allcode a parent table for follow_up table? – Hardeep Singh Sep 21 '17 at 12:26
  • Set ordering on "follow_up_id" column of "follow_up" table – Rupal Javiya Sep 21 '17 at 12:30
  • 1
    Not a bad effort, but see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Sep 21 '17 at 12:30
  • I really don't understand parent child-relationship, but `stage_id` and `conclusion_id` of `follow_up` table are nothing but `code_id` of `allcode` – Shantaram Tupe Sep 21 '17 at 12:31
  • @Rupal the result I type here so I missed somthing , I will update my question – Shantaram Tupe Sep 21 '17 at 12:38
  • @Rupal the result I want is to be **group by** `stage_id`, **order by** `srno` of `allcode` and last stage of `follow_up` table – Shantaram Tupe Sep 21 '17 at 12:45
  • Have you tried "group by follow_up.stage_id order by ast.srno, follow_up.follow_up_id" ? – Rupal Javiya Sep 21 '17 at 12:53
  • @Rupal I have tried but not getting last/recent record from `follow_up` – Shantaram Tupe Sep 21 '17 at 12:57
  • The title is enough: *"MySQL retrieve last record in JOIN with group by"* -- the `GROUP BY` queries **do not** return records retrieved from the database. The rows they return are **generated** but them using the records retrieved from the database. The action in the title of your question is simply not possible. Not because of MySQL but because of how SQL works. Search for similar queries under the [tag:greatest-n-per-group] tag. – axiac Sep 21 '17 at 13:07
  • My results don't match your expected: the conclusions I think should be 2:`Next`, 4:`call back` 5:`call back` – xQbert Sep 21 '17 at 13:17
  • sorry for that I did not posted values of `conclusion_id` from `allcode` table since my purpose was to `group by` `stage_id` only BTW it is 2:`ISO`, 4:`Call Back` 5:`Call Back` – Shantaram Tupe Sep 21 '17 at 13:22

2 Answers2

1

DEMO Includes my answer, original question with full group by needed, and Reupal's answer in demo. You were missing the values in your sample data for conclusionID so I just created them based on ID (now updated to ISO, Callback but missing 98.)

and my results don't match yours in this column; but I believe your expected results are in error.

Seems like you want the max follow_up_ID for each stage_ID when multiple stage_ID's exist

This can be handled by a derived table/inline view getting that max follow_UP_ID grouped by the stage_ID and a joining it back to your set. to limit results to include only the max follow_Up_ID by stage_Id.

I'm also not a fan of mySQL's extended group by and prefer including all columns not aggregated in the select in the group by. Using the extended group by tends to hide potential problems. In this case grouping by just the ast.code_name allowed the engine to select a non distinct value from the other columns. You ended up not getting the desired results and furthermore it hide the fact you would get multiple records in your query were it not for the extended group by use/misuse.

SELECT f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, 
ast.code_name as stage, ac.code_name as conclusion, max(l.logs_time)  
from follow_up f 
join logs l on l.tran_id = f.follow_up_id 
join allcode ast on ast.code_id = f.stage_id 
join allcode ac on ac.code_id = f.conclusion_id 
JOIN SELECT max(follow_up_ID) MFID, stage_ID 
      FROM follow_up 
      GROUP BY stage_ID) Z 
  on f.follow_up_ID = Z.MFID 
 and F.Stage_ID = Z.Stage_ID
WHERE l.transaction='FLWUP' and f.client_id = 101 
GROUP BY f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, 
ast.code_name , ac.code_name
ORDER BY ast.srno;
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • i will give try and let you know, meanwhile any other solution to this – Shantaram Tupe Sep 21 '17 at 12:58
  • thanks working now, I tried this before but I use **select** after `=` sign and gave me error of **multiple returning column**, so I did not tried further, – Shantaram Tupe Sep 21 '17 at 13:12
  • why **group by** so many columns, `group by ast.code_name` giving same result – Shantaram Tupe Sep 21 '17 at 13:18
  • Take your original query and run it with all the fields I have in the group by. You'll see that you get 5 records instead of 3. the group by was hiding records from you becuase you didn't group by all the unique columns. http://rextester.com/QUOH30123. mySQL later versions have this feature disabled by default. It's a nice feature to have but it often leads to confusion until you fully understand aggregation and group bys. you should ONLY group by fewer fields when all other values in the fields returned would be the same. This was not the case in your query; thus part of your problem – xQbert Sep 21 '17 at 13:21
  • Since I'm using hibernate, I've very little knowledge of **sql queries**, so difficult to understand **queries** – Shantaram Tupe Sep 21 '17 at 13:27
  • the rextester example I linked shows this situation. – xQbert Sep 21 '17 at 13:31
  • I took your sample data above and manually created the tables. So if you follow the link you'll see I have the 3 tables involved, and i populated w/ sample data from what you have up top. I assumed data types based on the sample data which gets us "close enough" to ensure the query is working as desired. though I'm not sure I really understand your question. Out of form, I prefix the tables with the SO question number and drop the tables as part of the script. – xQbert Sep 21 '17 at 13:54
0

Try below, notice ordering and group by sequence.

select f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, 
ast.code_name as stage, ac.code_name as conclusion, max(l.logs_time)  
from follow_up f 
join logs l on l.tran_id = f.follow_up_id 
join allcode ast on ast.code_id = f.stage_id 
join allcode ac on ac.code_id = f.conclusion_id 
where l.transaction='FLWUP' and f.client_id = 101 
group by follow_up.stage_id order by ast.srno, follow_up.follow_up_id DESC;

This should works, and if its not then you should search like how to set ordering on multiple column.

Ref. article- SQL multiple column ordering

Rupal Javiya
  • 591
  • 5
  • 14