Reputation: 1093
select DISTINCT first_name, last_name, picture, last_active, id_participante1, id_participante2, id_user, [message], dataHora from chat_b_users inner join utilizadores
on chat_b_users.id_participante2 = utilizadores.id_user
left join chat_talks on chat_b_users.id_chat = chat_talks.id_chat
where id_participante1 = 1
union all
select DISTINCT first_name, last_name, picture, last_active, id_participante1, id_participante2, id_user, [message], dataHora from chat_b_users inner join utilizadores
on chat_b_users.id_participante1 = utilizadores.id_user
left join chat_talks on chat_b_users.id_chat = chat_talks.id_chat
where id_participante2 = 1 order by last_active DESC
how can i select a distinct value?
I need to return all this data even nulls but on of each user, how can i make this?
results:
as you can see in the image, i've two chats from the same user, i only want one of each.
Upvotes: 0
Views: 114
Reputation: 78
try this : add your column list(s) in place of in the code to identify which row you want to display.
SELECT first_name, last_name, picture, last_active, id_participante1, id_participante2, id_user, [message], dataHora
(
select DISTINCT first_name, last_name, picture, last_active, id_participante1, id_participante2, id_user, [message], dataHora ,
ROW_NUMBER (PARTITION BY <add_yr_colist> ORDER BY Last_Avtive DESC) AS RNUM
from chat_b_users inner join utilizadores
on chat_b_users.id_participante2 = utilizadores.id_user
left join chat_talks on chat_b_users.id_chat = chat_talks.id_chat
where id_participante1 = 1 OR id_participante2 = 1
)TVC WHERE RNUM = 1
Upvotes: 2
Reputation: 15387
You need to use UNIION
instead of UNION ALL
as below:
select DISTINCT first_name, last_name, picture, last_active, id_participante1, id_participante2, id_user, [message], dataHora from chat_b_users inner join utilizadores
on chat_b_users.id_participante2 = utilizadores.id_user
left join chat_talks on chat_b_users.id_chat = chat_talks.id_chat
where id_participante1 = 1
union
select DISTINCT first_name, last_name, picture, last_active, id_participante1, id_participante2, id_user, [message], dataHora from chat_b_users inner join utilizadores
on chat_b_users.id_participante1 = utilizadores.id_user
left join chat_talks on chat_b_users.id_chat = chat_talks.id_chat
where id_participante2 = 1 order by last_active DESC
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
Difference betwwen UNION & UNION ALL
Upvotes: 0