SELECT within a SELECT - Get last 100 entries

[vdloo]

Hello everyone,

 

Before I start, I have:  PHP v 5.3.0, MySQL v5.1.36.

 

I have numbers in my database table which is structured like this:

 

CREATE TABLE results (
id int(11) NOT NULL auto_increment,
number int(3) NOT NULL,
type varchar(50) NOT NULL,
datetime DATETIME NOT NULL,
PRIMARY KEY (id)
);

 

For now I am only using numbers 0 - 5 and the numbers are ordered by number; ascending in the table...

 

I am trying to count the number of times each individual number was entered into the database over the last 100 entries.

 

So I would be able to say, over the last 100 entries, 1 was entered 20 times, 3 was entered 8 times etc..

 

This is the sql statement that I have:

 

SELECT DISTINCT COUNT(number) FROM (SELECT number FROM results WHERE type='type1' ORDER BY datetime DESC LIMIT 100)

 

When I try to loop through the result, I don't get an error I only get this warning:

 

"Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in ... "

 

Any help or ideas would be greatly appreciated.

 

Thanks.

 

[Ken2k7]

Try:

SELECT number, COUNT(*) FROM results GROUP BY number ORDER BY datetime DESC LIMIT 100;

[vdloo]

Thanks for the reply Ken2k7, but this didn't work...

 

This query does the basics and returns the last 100 numbers that were entered into the database:

 

SELECT number FROM results WHERE type='type1' ORDER BY datetime DESC LIMIT 100

 

Now I want to count the distinct numbers in that result. Can you maybe do it in 2 separate queries?

 

Or maybe read the result into an array and then count the unique numbers in the array?

 

Thanks.

[Ken2k7]

I forgot to add in a type check. >_<

 

SELECT number, COUNT(*) c FROM results WHERE type='type1' GROUP BY number ORDER BY datetime DESC LIMIT 100;