I'm trying to do an SQL query that I do not know if it can be done, but my purpose is to obtain the records depending on the id's, sorting them by latest date and limiting them to 1 to initially only show a record and then in another view show all without the limit.
So far I have this query:
SELECT * FROM messages
WHERE idprincipal = 28 || 26 AND idfinal = 26 || 28
ORDER BY sent ASC
How can I give that query a limit 1 ?
You have a problem with the logic you use in your SQL query. You use the or logical operator as if it were used to select more than one element, when that is not the case. In addition, you must order in a descending order so that the first record (the one you are left with) is the largest and the later ones are lower than this one.
To filter by several values of a field you must use the operator IN .
A correct way to do it would be:
SELECT *
FROM messages
WHERE
idprincipal IN (28, 26)
AND
idfinal IN (26, 28)
ORDER BY sent DESC
LIMIT 1
Here you can see an online version. Uncomment ( -- of LIMIT ) to obtain only the most recent one.
Detailed description of the problem:
As you can see in the documentation of order of operators Y (& &) associations are resolved before the O (||), so your query would be:
idprincipal = 28 || 26 AND idfinal = 26 || 28
( idprincipal = 28 ) || 26 AND ( idfinal = 26 ) || 28
( idprincipal = 28 ) || ( 26 AND ( idfinal = 26 ) ) || 28
28
In the last line there is only 28 since in a condition O as soon as one of the parts is true, the result is true regardless of the rest, and since 28 is considered true , you will get all the records, without effective filtering.
Here you can see the result online.
Another example to better illustrate the problem:
idprincipal = 28 || 26 AND idfinal = 26
( idprincipal = 28 ) || 26 AND ( idfinal = 26 )
( idprincipal = 28 ) || ( 26 AND ( idfinal = 26 ) )
( idprincipal = 28 ) || ( idfinal = 26 )
Initially you might think it's going to show you the records whose idprincipal is (28 O 26) Y the idfinal is 26 , but at the end you will get the records whose idprincipal is 28 O the idfinal is 26 .
( 26 AND ( idfinal = 26 ) ) is translated into ( idfinal = 26 ) because in a condition Y if one of the operators is true the condition depends solely on the other operator. In this case 26 is considered true.
Here is an online version where you can check the result.
I recommend taking special care with the precedence of operators and, when in doubt, use parentheses to group them.
Are you using SQL right?
Therefore you can NOT use Limit , instead you have Top .
SELECT TOP 1 *
FROM messages
WHERE
idprincipal IN (28, 26)
AND
idfinal IN (26, 28)
ORDER BY sent DESC
Source: link
If you used MYSQL , as they say in other answers, you have to use LIMIT .
SELECT *
FROM messages
WHERE
idprincipal IN (28, 26)
AND
idfinal IN (26, 28)
ORDER BY sent DESC
LIMIT 1
To what I understand, for what you want to do you can use:
SELECT * FROM messages ORDER BY sent DESC LIMIT 1;
To limit the query to a single result always showing the most recent one. So when you check your results, only the most recent message will appear. To get all the messages you can use a different query.
SELECT * FROM messages WHERE idprincipal = 28 DESC;