Hi,

im stuck here and i don't know how to fix it.

I have a db table which has users ID, user grade and date when someone has voted for that user (3 fields).

Im trying to read the user that has the highest average grade for todays date, limited to one.
But the problem is that i want to read only users that have 5 or more votes.

My query looks like this, but im getting an error:

$query = "SELECT idusers, AVG(votes) AS Grade FROM rank WHERE (data = '{$dbDate}') AND ((SELECT count(ID) + 1 FROM rank) AS tmpcount WHERE tmpcount>4) GROUP BY idusers ORDER BY Grade DESC LIMIT 1";

Red part is where the problem is.
Without the >4 clause this query is working ok, but i need to count the ID's.

Any help?
************************
EDIT:
Nevermind solved it:
$query = "SELECT idusers, AVG(votes) AS Grade FROM rank WHERE (data = '{$dbDate}') GROUP BY idusers HAVING COUNT(ID) > 4 ORDER BY Grade DESC LIMIT 1";

How about:

SELECT
    idusers,
    AVG(votes) AS Grade
FROM
    rank
WHERE 
    data = '{$dbDate}' AND
    (
        SELECT
            count(ID)
        FROM
            rank
    ) >= 5
GROUP BY
    idusers
ORDER BY
    Grade DESC
LIMIT
    1

Yes, that's another way to do it.
I tried that at first but i missed the syntax somewhere because im not very familiar with advanced sql statements.

Hey
I need a little help again.
I have a table that looks like this:

###################################
# ID # Type # Info  # Expires #
###################################
# 1 # Data1 # Info1 # timestamp #
# 2 # Data1 # Info2 # timestamp #
# 3 # Data1 # Info3 # timestamp #
# 4 # Data2 # Info4 # timestamp #
# 5 # Data3 # Info5 # timestamp #

Im trying to group by "Type" and order by "ID" and "Expires" descending.
But everytime i get first row as a result which has ID = 1 instead of third row.

PHP Code:

$stamp = time();
query = "SELECT * FROM table1 WHERE Expires >= {$stamp} GROUP BY Type Order BY ID DESC, Expires DESC"; 


This gives me 1st, 4th and 5th rows.
timestamp in third row is greater than the first row.

Any suggestions?

What purpose is the "group by type" supposed to be achieving? Is there more to the query than you're decsribing?

Im trying to get the last inserted rows of every type, 3rd, 4th and 5th row in this case.
I guess grouping results isn't what i need.

With 3 separate queries:

SELECT * FROM table1 WHERE Type = 'Data1' ORDER BY ID DESC LIMIT 1;
SELECT * FROM table1 WHERE Type = 'Data2' ORDER BY ID DESC LIMIT 1;
SELECT * FROM table1 WHERE Type = 'Data3' ORDER BY ID DESC LIMIT 1;

I don't really know how many types i'll have so i need to get an array in one query.

SOLVED:

SELECT * FROM (SELECT * FROM table1 ORDER BY ID DESC) t GROUP BY Type;