Topic: 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:

Code: [Select]

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:

Code: [Select]

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:

Code: [Select]

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;