Get all records without repeating id

Question

Get all records without repeating id

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I want to make a query to get all the data from my table, but without the idPrincipal being repeated, but getting the others idPrincipal

that is, in my table I have the field

id auto increment
idPrincipal
idFinal
sent
msg

the idPrincipal can have id 1 or 2 or 3 infinity of times, but I want to make a query where only show 1 time with the last data they have.

my query that I made was:

    SELECT idFinal, sent, msg 
DISTINCT idPrincipal from messages
WHERE idFinal = 28 
ORDER BY sent DESC 
LIMIT 1

but when executing the query it marks me error, I'm using MYSQL

EDITED

My database consists of the following already with real data ...

tabla usuarios 
idUsuario,
nombre,

Tabla messages
idMsg,
idEmitter FK usuarios
idReceiver FK usuarios
messages,
nombre,
sent DATE
seenReceiver

I want the messages that have idReceiver with seenReceiver = 0 but not showing the last message of idReceiver without repeating it since the way I generated the query generated the records and printed all the that they had seen 0

an example of my result was

idEmitter 1 'mensaje' 'sent' idReceiver 28
idEmitter 1 'mensaje' 'sent' idReceiver 28
idEmitter 1 'mensaje' 'sent' idReceiver 28
idEmitter 3 'mensaje' 'sent' idReceiver 28
idEmitter 3 'mensaje' 'sent' idReceiver 28
idEmitter 3 'mensaje' 'sent' idReceiver 28
idEmitter 5 'mensaje' 'sent' idReceiver 28
idEmitter 5 'mensaje' 'sent' idReceiver 28
idEmitter 5 'mensaje' 'sent' idReceiver 28

I just want the last message sent by the idEmitter to appear

idEmitter 1 'mensaje' 'sent' idReceiver 28
idEmitter 3 'mensaje' 'sent' idReceiver 28
idEmitter 5 'mensaje' 'sent' idReceiver 28

I hope someone can help me

mysql php

asked by Cesar Gutierrez Davalos 14.07.2017 в 17:02

source

2 answers

1

You could try the next query

SELECT * FROM 'tabla' WHERE idPrincipal = ? AND idFinal = ? ORDER BY sent DESC LIMIT 1

Change the question marks by the corresponding IDs.

When I initiate LIMIT 1 I'm saying that it only shows one result. The first being the most recent insertion according to the IDs we designate, results in the last message according to date and also with this query you get all columns.

answered by 14.07.2017 в 17:43

Avoid saving credentials in the browser There are no primary or candidate keys in the table referenced

score 1 · Accepted Answer

I will directly support you in idMsg , you have not specified it in your last edition but I understand that it is a autonumérico, this assures me which is the oldest or newest in a more direct and safe way (x date we would always have the problem in the case of equal dates).

The solution is to generate a subquery with the maximum idMsg for each idReceiver and idEmitter , then simply join this subquery, something like this:

SELECT  m.idEmitter, 
        m.messages, 
        m.sent, 
        m.idReceiver 
    FROM messages m
    INNER JOIN ( SELECT idEmitter,
                        idReceiver,
                        max(idMsg) AS idMsg
                        FROM messages
                        WHERE seenReceiver = 0
                        GROUP BY idEmitter, idReceiver
        ) mm
        ON m.idMsg = mm.idMsg
        AND m.idEmitter = mm.idEmitter
        AND m.idReceiver = mm.idReceiver
    WHERE m.idReceiver = 28 
          AND seenReceiver = 0

I hope you find it useful ..