SQL Query return one of each

Question

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.

I see, the `DISTINCT` doesn't work because the combination are different. Your timestamp makes them NOT DISTINCT. Try `MAX` with the `last_active` and see if it helps. Have a look at [this answer](http://stackoverflow.com/questions/5391564/how-to-use-distinct-and-order-by-in-same-select-statement) — , Sep 10 '14 at 09:47
i need both results from each query, i've tried and remove one that it should't remove — Severiano, Sep 10 '14 at 10:10

score 2 · Accepted Answer · answered Sep 10 '14 at 10:03

2

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

answered Sep 10 '14 at 10:03

Rishabh

78
7

what is this? – Severiano Sep 10 '14 at 10:08
1

add a list of the columns which will identify a one particular window.say in your example you need to display message OK for user_Id = 40 , then replace with user_id which will partition the rows in different windows based on user_id values. Hope this explains. – Rishabh Sep 10 '14 at 10:11
ROW_NUMBER (PARTITION BY User_id ORDER BY Last_Active DESC) – Rishabh Sep 10 '14 at 11:24
SELECT * ( select DISTINCT first_name, last_name, picture, last_active, id_participante1, id_participante2, id_user, [message], dataHora, ROW_NUMBER (PARTITION BY id_user ORDER BY last_active 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 ||| still has error – Severiano Sep 10 '14 at 11:28
my mistake! but the erro continues in BY – Severiano Sep 10 '14 at 11:37
1

well my mistake this time : ROW_NUMBER Should be like this ROW_NUMBER() OVER (PARTITION BY id_user ORDER BY last_active DESC) – Rishabh Sep 10 '14 at 11:41

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

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

edited May 23 '17 at 12:29

Community

1
1

answered Sep 10 '14 at 09:57

Amit

15,217
8
46
68

the same as the other one – Severiano Sep 10 '14 at 10:05
@Severiano As i see there is `message and dataHora ` is different from the previous one. So it is displaying twice. – Amit Sep 10 '14 at 10:06
it's displaying twice but different messages, if i had 5 messages, it would display 5 times the same user, and i only want the last one – Severiano Sep 10 '14 at 10:08
@Severiano What is the primary key on the table – Amit Sep 10 '14 at 10:22
id_user is the primary key – Severiano Sep 10 '14 at 10:29

SQL Query return one of each

2 Answers2