Peter Peter - 12 days ago 4
MySQL Question

Getting sorted mysql records with unique value

I'm trying to get some values from a database but limit it to 1 per account, and I'm having trouble getting it working how I want it.

Here is the query as it stands, which gets the emails in the correct order, and ignores the account:

SELECT ID FROM MailSent
WHERE DateSent IS NULL AND Valid = 1 ORDER BY Priority DESC LIMIT 14


I would like the highest priority record for each account (
EmailID
). I could just loop through the results and discard any duplicates, but then the actual limit would be a lot lower than the intended one.

Here's a few things I've tried:

SELECT ID, DISTINCT(EmailID) FROM MailSent
WHERE DateSent IS NULL AND Valid = 1 ORDER BY Priority DESC LIMIT 14
// error

SELECT DISTINCT(EmailID), ID FROM MailSent
WHERE DateSent IS NULL AND Valid = 1 ORDER BY Priority DESC LIMIT 14
// still has duplicates

SELECT ID FROM MailSent
WHERE DateSent IS NULL AND Valid = 1 ORDER BY Priority DESC GROUP BY EmailID LIMIT 14
// error

SELECT ID FROM MailSent
WHERE DateSent IS NULL AND Valid = 1 GROUP BY EmailID ORDER BY Priority DESC LIMIT 14
// wrong priority


As a bonus, but not required as it might be quite hard to do, it'd be nice having it limited to a user defined amount instead of just 1.

Answer

Just use variables

SELECT ID, EmaailID
FROM (
       SELECT ID, EmailID,
              @rn := if(@email = EmailID,
                        @rn + 1,
                        if(@email := EmailID, 1, 1)
                       ) as rn
       FROM MailSent 
       CROSS JOIN (SELECT @email := 0, @rn := 0) as param
       WHERE DateSent IS NULL AND Valid = 1
       ORDER BY Priority Desc
       ) T
WHERE T.rn = 1

Now if you want limit result for user, you need a settings table or a query from somewhere. Just replace previous WHERE for:

   JOIN (SELECT userID, numberofRows
         FROM SettingTable) P
     ON T.ID = P.userID
    AND T.rn <= P.numberofRows
Comments