Getting sorted mysql records with unique value

Question

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.

`GROUP BY` doesn't help here. You need records from the table but `GROUP BY` doesn't return records from a table, it **creates** the rows it returns using the records it fetches from tables. — axiac, Nov 23 '16 at 00:44
Take a look at [this answer](http://stackoverflow.com/a/28090544/4265352) on a similar question. You can create your own query using the query explained there. — axiac, Nov 23 '16 at 00:46
Thanks, I was trying it out but still not much luck, I'll update the question with the attempt if I have a bit of trouble with Juans version :) — Peter, Nov 23 '16 at 00:56

Juan Carlos Oropeza · Accepted Answer · 2016-11-23T01:07:07.470

1

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

edited Nov 23 '16 at 01:07

answered Nov 23 '16 at 00:53

Juan Carlos Oropeza

47,252
12
78
118

Thanks, I've never seen mysql done like that haha, for the record I think you missed a bracket after `if(@email := EmailID, 1, 1) as rn`, just checking it should be `if(@email := EmailID, 1, 1)) as rn` right? I'll generate a few more emails to test it out, so far so good though :) – Peter Nov 23 '16 at 01:00
However, when you mention a settings table, do you mean one to keep track of how many pending emails there are per account? – Peter Nov 23 '16 at 01:04
Sorry Im not clear about the email context here. You mention you want limit the number of result, so I guess you need have that information in someplace. And yes, I miss one bracket, bad Juan :$ – Juan Carlos Oropeza Nov 23 '16 at 01:06
Haha, I just wasn't sure if I got that bracket correct :P Also sorry for not making it clearer, I'd only meant a global amount for every account (and now realise I can just do `T.rn <= 2`), thanks for the more advanced method though, it may come in useful in the future :) – Peter Nov 23 '16 at 01:13

Getting sorted mysql records with unique value

1 Answers1