Query para obter as ultimas mensagens de cada conversa em privado

Question

Boa noite pessoal, estou desenvolvendo um app com React Native similar ao WhatsApp, e então preciso na tela principal mostrar a última mensagem que X usuário mandou ou recebeu do seu contato.

A tabela no banco de dados, já com os registros (para testes) que tenho é algo semelhante a isso:

id	my_id	to_user	message
1	1	2	a1
2	1	2	a2
3	2	1	a3
4	2	1	a4
5	3	2	a5
6	1	2	a6
7	4	3	a7
8	1	2	a8
9	2	1	a9
10	5	1	a10

No app após o login é guardado o meu id que nesse cenário é o 1 e depois na tela inicial tem que ser mostrada as últimas mensagens, seja ela mandada por mim ou enviada para mim, nesse exemplo de tabela acima, os dados que precisam ser retornados de forma única seriam:

id	my_id	to_user	message
9	2	1	a9
10	5	1	a10

Ou seja a última conversa entre o usuário 1 e 2 foi a do id 9 e o usuário 5 mandou mensagem para o usuário 1 e essa até então foi a última mensagem.

Tentei várias formas, mas infelizmente não consegui, alguém poderia me ajudar?

SELECT * FROM test WHERE my_id = 1 OR to_user = 1

Nessa query acima retornar todas as mensagens enviadas por mim ou para mim, mas precisaria da verificação sobre a última mensagem enviada nessa conversa.

obs: existe nessa tabela a coluna created_at quer seria a data de criação da mensagem, tentei pegar a max date mas sem sucesso.

Answer 1

Bom dia,

Experimente fazer uns testes utilizando a função Row_Number mais ou menos dessa forma:

with CTE_RN as
(
    SELECT 
        *, 
        ROW_NUMBER() OVER(
            PARTITION BY CASE WHEN my_id = 1 then to_user else my_id end
            ORDER BY created_at) as RN
    FROM test 
    WHERE my_id = 1 OR to_user = 1
)

select * from CTE_RN where RN = 1

Espero que ajude

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 2

Funcionou perfeitamente Gapimex, se não se importar poderia me explicar o funcionamento por trás disso? não tenho conhecimento sobre o ROW_NUMBER(), OVER PARTITION BY, CASE WHEN.

Answer 3

A Row_Number é uma função de classificação. Confira a página abaixo sobre ela:

https://docs.microsoft.com/pt-br/sql/t-sql/functions/ranking-functions-transact-sql?view=sql-server-ver15

Só pra complementar, o Case foi utilizado para que seja utilizado o valor da coluna to_user quando o usuário em questão enviou a mensagem, ou o valor da my_Id quando recebeu.

Espero que ajude

---

Assinatura: http://www.imoveisemexposicao.com.br