Complex SQL Queries (Sort using 2 columns)

Question

I execute SQL and get the results below.

select vital_history_id,vital_id,patient_id,measurement_datetime,create_datetime
from t_patient_vital_history
where patient_id = 54
group by vital_id,create_datetime
order by measurement_datetime desc, create_datetime desc
limit 100 offset 0

results-1 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　But I want to get the results like this.

results-2 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　This is DDL and DML.

create table t_patient_vital_history (
  vital_history_id bigint unsigned not null
  , vital_id bigint unsigned not null
  , patient_id bigint unsigned not null
  , measurement_datetime datetime
  , sbp double
  , dbp double
  , temperature
  , pulse double
  , spo2 double
  , weight double
  , bmi double
  , abdominal double
  , height_measurement double
  , head_measurement double
  , chest_measurement double
  , action varchar(20)
  , is_deleted tinyint default 0 not null
  , create_id varchar(20) not null
  , create_name varchar(100) not null
  , create_datetime datetime(3) not null
  , update_id varchar(20) not null
  , update_name varchar(100) not null
  , update_datetime datetime(3) not null
  , constraint t_patient_vital_history_PKC primary key (vital_history_id)
);


INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (107,66,54,'2018-06-25 17:15:32',900,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'register',0,'1','Mike','2018-05-25 17:15:40.638','1','Mike','2018-05-25 17:15:40.638');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (119,69,54,'2018-07-25 18:24:15',22,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'register',0,'1','Mike','2018-05-25 18:24:57.772','1','Mike','2018-05-25 18:24:57.772');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (129,69,54,'2018-07-25 18:24:15',22,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-25 18:37:19.971','1','Mike','2018-05-25 18:37:19.971');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (130,69,54,'2018-07-25 18:24:15',22,1,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-25 18:37:24.128','1','Mike','2018-05-25 18:37:24.128');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (131,69,54,'2018-07-25 18:24:15',22,1,2,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-25 18:37:31.503','1','Mike','2018-05-25 18:37:31.503');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (134,69,54,'2018-07-25 18:24:15',22,1,2,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-25 18:44:42.444','1','Mike','2018-05-25 18:44:42.444');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (135,69,54,'2018-07-25 18:24:15',22,1,2,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-25 18:44:49.518','1','Mike','2018-05-25 18:44:49.518');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (141,69,54,'2018-07-25 18:24:15',22,1,2,NULL,12,5,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-28 17:47:22.835','1','Mike','2018-05-28 17:47:22.835');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (147,66,54,'2018-06-25 17:15:32',90,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-28 19:48:00.159','1','Mike','2018-05-28 19:48:00.159');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (164,69,54,'2018-07-25 18:24:15',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'削除',1,'1','Mike','2018-05-30 12:07:12.563','1','Mike','2018-05-30 12:07:12.563');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (168,90,54,'2018-10-01 01:00:59',1,1,1,1,1,1,10000,1,1,1,1,'register',0,'1','Mike','2018-05-30 13:11:29.756','1','Mike','2018-05-30 13:11:29.756');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (1231,90,54,'2018-10-01 01:00:59',1,1,1,1,1,1,NULL,1,NULL,1,1,'update',0,'1','Mike','2018-06-11 16:32:33.263','1','Mike','2018-06-11 16:32:33.263');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (1302,90,54,'2018-10-01 01:00:59',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'削除',1,'1','Mike','2018-06-15 13:16:18.100','1','Mike','2018-06-15 13:16:18.100');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (1323,66,54,'2018-06-25 17:00:32',90,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-06-15 16:40:40.627','1','Mike','2018-06-15 16:40:40.627');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (1340,66,54,'2018-06-25 17:20:32',90,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-06-15 18:33:20.112','1','Mike','2018-06-15 18:33:20.112');

First, sort by measurement_datetime desc.　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Next, sort by create_datetime desc keeping sort by measurement_datetime desc. 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　How to sort this queries using measurement_datetime and create_datetime?　　　　　　　　　　 please advice me.

Your query seems proper, and the sort is working as expected, what do you exactly want? — Jinesh Shah
– Jinesh Shah, Commented Jun 19, 2018 at 3:14
I means, I already got results-1. But I expected to get results-2. — donuts
– donuts, Commented Jun 19, 2018 at 3:18
Why do you want a row with measurement_datetime =2018-06-25 17:00:32 before measurement_datetime =2018-06-25 17:15:32? — M Khalid Junaid
– M Khalid Junaid, Commented Jun 19, 2018 at 5:25
it displays on medical application. we need to sort by create_datetime within same vital_id. — donuts
– donuts, Commented Jun 20, 2018 at 7:01

Vinicius Grippa · Accepted Answer · 2018-06-19 03:16:00Z

You can achieve what you are looking for sorting by vital_id first and then by vital_history_id.

mysql > select vital_history_id,vital_id,patient_id,measurement_datetime,create_datetime
    -> from t_patient_vital_history
    -> where patient_id = 54
    -> group by vital_id,create_datetime
    -> order by vital_id desc, vital_history_id desc, measurement_datetime desc, create_datetime desc
    -> limit 100 offset 0;
+------------------+----------+------------+----------------------+-------------------------+
| vital_history_id | vital_id | patient_id | measurement_datetime | create_datetime         |
+------------------+----------+------------+----------------------+-------------------------+
|             1302 |       90 |         54 | 2018-10-01 01:00:59  | 2018-06-15 13:16:18.100 |
|             1231 |       90 |         54 | 2018-10-01 01:00:59  | 2018-06-11 16:32:33.263 |
|              168 |       90 |         54 | 2018-10-01 01:00:59  | 2018-05-30 13:11:29.756 |
|              164 |       69 |         54 | 2018-07-25 18:24:15  | 2018-05-30 12:07:12.563 |
|              141 |       69 |         54 | 2018-07-25 18:24:15  | 2018-05-28 17:47:22.835 |
|              135 |       69 |         54 | 2018-07-25 18:24:15  | 2018-05-25 18:44:49.518 |
|              134 |       69 |         54 | 2018-07-25 18:24:15  | 2018-05-25 18:44:42.444 |
|              131 |       69 |         54 | 2018-07-25 18:24:15  | 2018-05-25 18:37:31.503 |
|              130 |       69 |         54 | 2018-07-25 18:24:15  | 2018-05-25 18:37:24.128 |
|              129 |       69 |         54 | 2018-07-25 18:24:15  | 2018-05-25 18:37:19.971 |
|              119 |       69 |         54 | 2018-07-25 18:24:15  | 2018-05-25 18:24:57.772 |
|             1340 |       66 |         54 | 2018-06-25 17:20:32  | 2018-06-15 18:33:20.112 |
|             1323 |       66 |         54 | 2018-06-25 17:00:32  | 2018-06-15 16:40:40.627 |
|              147 |       66 |         54 | 2018-06-25 17:15:32  | 2018-05-28 19:48:00.159 |
|              107 |       66 |         54 | 2018-06-25 17:15:32  | 2018-05-25 17:15:40.638 |
+------------------+----------+------------+----------------------+-------------------------+
15 rows in set (0.00 sec)

And here is the query:

select vital_history_id,vital_id,patient_id,measurement_datetime,create_datetime
from t_patient_vital_history
where patient_id = 54
group by vital_id,create_datetime
order by vital_id desc, vital_history_id desc, measurement_datetime desc, create_datetime desc
limit 100 offset 0;

Glad to hear that. If you possible, please validate the answer

Collectives™ on Stack Overflow

Complex SQL Queries (Sort using 2 columns)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related