Order By multiple columns (Datetime and varchar)-CodePudding

I have the following query about a table that records conversations and stores the name, the chatid and the date and time of the conversation. My problem is that sometimes people write at the same time and the conversations are mixed, I have tried different ways to perform the query and I have not succeeded. I attach the query and an example of the returned query and the expected query, thanks!

SELECT  WhatsApp.username
      , WhatsApp.chatid
      , WhatsApp.fecha_reg
FROM wsappbotLog AS WhatsApp
ORDER BY WhatsApp.fecha_reg, WhatsApp.chatid

The result is as follows

| username |  chatid |       fecha_reg          |
| -------- | ------- |  ----------------------  |
|   Sara   |  57316  |  2022-08-25 08:15:29.053 |
|   Sara   |  57316  |  2022-08-25 08:23:51.280 |
|  Andrés  |  57320  |  2022-08-25 08:32:26.910 |
|  Andrés  |  57320  |  2022-08-25 08:32:41.587 |
|   Sara   |  57316  |  2022-08-25 08:33:24.813 |
|   Sara   |  57316  |  2022-08-25 08:35:06.670 |
|   Tom    |  57312  |  2022-08-25 08:35:46.960 |
|   Sara   |  57316  |  2022-08-25 08:36:39.000 |
|   Sara   |  57316  |  2022-08-25 08:36:52.160 |
|  James   |  57321  |  2022-08-25 08:38:39.670 |
|  James   |  57321  |  2022-08-25 08:39:14.797 |
|  James   |  57321  |  2022-08-25 08:39:15.187 |

And the expected result would be

| username |  chatid |       fecha_reg          |
| -------- | ------- |  ----------------------  |
|   Sara   |  57316  |  2022-08-25 08:15:29.053 |
|   Sara   |  57316  |  2022-08-25 08:23:51.280 |
|   Sara   |  57316  |  2022-08-25 08:33:24.813 |
|   Sara   |  57316  |  2022-08-25 08:35:06.670 |
|   Sara   |  57316  |  2022-08-25 08:36:39.000 |
|   Sara   |  57316  |  2022-08-25 08:36:52.160 |
|  Andrés  |  57320  |  2022-08-25 08:32:26.910 |
|  Andrés  |  57320  |  2022-08-25 08:32:41.587 |
|   Tom    |  57312  |  2022-08-25 08:35:46.960 |
|  James   |  57321  |  2022-08-25 08:38:39.670 |
|  James   |  57321  |  2022-08-25 08:39:14.797 |
|  James   |  57321  |  2022-08-25 08:39:15.187 |

CodePudding user response：

Work out each chat's first datetime and order by that first.

WITH
  chat AS
(
  SELECT
    *,
    MIN(fecha_reg) OVER (PARTITION BY chatid) AS fecha_reg_min
  FROM
    wsappbotlog
)
SELECT
  *
FROM
  chat
ORDER BY
  fecha_reg_min,
  chatid,
  fecha_reg

CodePudding user response：

Work out the datetime of the first chat entry per user, fecha_reg_min, using the MIN window function. Then order by fecha_reg_min first (same as MatBailie said but without the CTE) followed by chatid since I assume you want to keep each chat together.

SELECT W.username
    , W.chatid
    , W.fecha_reg
    , MIN(W.fecha_reg) OVER (PARTITION BY W.chatid) fecha_reg_min
FROM wsappbotLog W
ORDER BY fecha_reg_min, W.chatid, W.fecha_reg;

Note the nice short table alias.

CodePudding user response：

You can sort by the WINDOW function FIRST_VALUE it takes also care of of the correct sorting with the date and chatid, to get rid of the problem when two chats start at the same time. With more people this could happen, so I added a new sara2 to the pot, to show it sorts correctly

SELECT
    [username], [chatid], [fecha_reg]
FROM wsappbotLog 
ORDER BY FIRST_VALUE([fecha_reg]) OVER (PARTITION BY [chatid] ORDER BY [fecha_reg], [chatid])

username | chatid | fecha_reg              
:------- | :----- | :----------------------
Sara     | 57316  | 2022-08-25 08:15:29.053
Sara     | 57316  | 2022-08-25 08:23:51.280
Sara     | 57316  | 2022-08-25 08:33:24.813
Sara     | 57316  | 2022-08-25 08:35:06.670
Sara     | 57316  | 2022-08-25 08:36:39.000
Sara     | 57316  | 2022-08-25 08:36:52.160
Sara2    | 57317  | 2022-08-25 08:15:29.053
Andrés   | 57320  | 2022-08-25 08:32:26.910
Andrés   | 57320  | 2022-08-25 08:32:41.587
Tom      | 57312  | 2022-08-25 08:35:46.960
James    | 57321  | 2022-08-25 08:38:39.670
James    | 57321  | 2022-08-25 08:39:14.797
James    | 57321  | 2022-08-25 08:39:15.187

db<>fiddle here