CODEWITHSUNDEEP
sqlsql-server
Severiano
Severiano

Reputation: 1093

SQL Query return one of each

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:

enter image description here

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

Answers (2)

Rishabh
Rishabh

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

Amit
Amit

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

Related Questions