Group by column y and return column with lowest value in column x

Question

I have a table with columns like id, mail, hits, time.

I want to get the 10 entries with the lowest hits and time values (best entries).

I used something like this:

SELECT * 
FROM tablename
ORDER BY hits, time ASC

In my table, there are many entries with the same mail addresses but I only want 10 entries with different mail addresses. So I tried the group by statement after which the data is mixed up.

How can I select the 10 best entries (lowest hits and time) with different mail addresses ?

Answer 1

This query I have not been test because there is no SQL-Fiddle example or a code to replicate your data.

But give this wild query a try.

SET @prevMail = '', @rownum=0;
SELECT b.mail, b.time, b.hits 
FROM (
    SELECT  
    a.mail,
    a.time,
    a.hits,
    CASE WHEN @prevMail = a.mail THEN @rownum :=@rownum+1 ELSE @rownum :=1 END AS rowNum,
    @prevMail:=a.mail
    FROM (
        SELECT mail, time, SUM(hits) AS hits
        FROM tablename 
        GROUP BY mail, time
        ORDER BY mail, time, SUM(hits) ASC
    ) AS a
) AS b
WHERE b.rowNum <= 10;

Here is explanation of the query:

1. Calculates the total hits for each combination (mail and time) then orders them by this order mail, time and the total hits.
2. From the list returned in the first step, we check each row of the query, if the email in the row is the same as the previous row then, I it increment the row number otherwise it sets it to 1 stating the there is a new combination starting.
3. Last query will give you only the first 10 records of each combination.

Answer 2

thanks guys, but your solutions did not work for me. but you guided me in the right direction :)

i found something that works

SELECT * 
FROM (
    SELECT * 
    FROM tablename
    ORDER BY hits ASC, time ASC 
) AS sub
GROUP BY mail