CODEWITHSUNDEEP
phpmysqldatabase
gregory
gregory

Reputation: 253

Order by before group by not giving real results

Hello i tried this query:

SELECT 
    * 
FROM 
    (
        SELECT 
            * 
        FROM 
            specials_companies_mails 
        WHERE 
            `toId` = 1 && parentId <> 0 
        ORDER BY 
            specials_companies_mails.mailId DESC
    ) AS b 
GROUP BY 
    b.parentId

i have two rows in the table:

mailID           subject        toId        parentId
3                test1          1           2
4                test2          1           2

and the result after the query:

mailID           subject        toId        parentId
3                test1          1           2

its mean that 

ORDER BY specials_companies_mails.mailId DESC

not working... its give me mailId 3 and not 4...

what to do?

tnx a lot

Upvotes: 0

Views: 45

Answers (4)

B. Desai
B. Desai

Reputation: 16446

If you want only latest record then try this:(I think you not need group by clause )

SELECT * 
        FROM 
            specials_companies_mails 
        WHERE 
            `toId` = 1 && parentId <> 0 
        ORDER BY 
            mailId DESC
       LIMIT 1

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

The GROUP BY doesn't care about the ORDER BY. The selected record will be totally non deterministic.

What you can do is find latest mainId for each parentId like this:

select a.* 
from specials_companies_mails AS a
left join specials_companies_mails AS b
    on  a.parentId = b.parentId
    and a.mailID < b.mailID
    and b.toId = 1 
    and b.parentId <> 0
WHERE b.parentId is null
    and a.toId = 1 
    and a.parentId <> 0

Upvotes: 2

Piotr Pasich
Piotr Pasich

Reputation: 2639

group by doesn't care if you have ordered records before or not. It gets information from first record saved in database, always. This is a painful thing that every developer faces, so don't worry.

You can solve the issue on a couple of ways.

  1. Split this into 2 separate queries - first query will order by and second will show you selected fields WHERE id IN (...)

  2. Join table specials_companies_mails with specials_companies_mails, but this won't be good for performance and is quite long.

  3. Don't group and write a part of code responsible for parsing data.

Upvotes: 1

Calos
Calos

Reputation: 1942

Try this:

SELECT `a`.* FROM `specials_companies_mails` AS `a`
LEFT JOIN `specials_companies_mails` AS `b` ON
    `a`.`mailID` =  `b`.`mailID` AND
    `a`.`mailID` <  `b`.`mailID`
WHERE `b`.`mailID` IS NULL

Upvotes: 2

Related Questions