Thread: Pulling total records and most recent data?

I'm trying to accomplish this with one SQL statement, but I guess I'm a little out of practice, as I'm coming up with nothing.

Let's say I have two entries in a database:

Code:

id	record	date			data1		data2
1	1234	2009-12-10 00:00:01	abc		def
2	1234	2009-12-10 02:00:00	def		ghi

Now, I want to pull the total records where id=1234 and I also want to pull the data from the most recent entry. I try:

Code:

SELECT *,COUNT(*) AS TOTAL FROM history WHERE record='1234' ORDER BY date DESC LIMIT 1

and it returns:

Code:

+--------+----------+---------------------+-------+-------+-------+
| log_id | record   | date                | data1 | data2 | total |
+--------+----------+---------------------+-------+-------+-------+
|      2 | 1234     | 2009-12-10 02:00:00 | abc   | def   |     2 |
+--------+----------+---------------------+-------+-------+-------+
1 row in set (0.00 sec)

It does this with ASC order, too. If I try it with MAX(date), I get the right date, but the data fields are not correct for the date.

--EDIT--
I found using a sub query will work, but is there a more efficient way?

Code:

SELECT *,(SELECT COUNT(*) FROM history WHERE record='1234') AS total FROM history WHERE record='1234' ORDER BY date DESC LIMIT 1

I would probably do something like:

Code:

SELECT COUNT(log_id) FROM history WHERE record = '1234' AND date = (SELECT MAX(date) FROM history WHERE record = '1234')

The last query you have there is about the easiest way to do this. I've written queries like this just this way.

And how efficient does it have to be with a 2 record table?

You should remove the asterick and put log_id or 1 in the aggregate function for improved performance:

Code:

SELECT *,(SELECT COUNT(1) FROM history WHERE record='1234') AS total FROM history WHERE record='1234' ORDER BY date DESC LIMIT 1

Originally Posted by jmurrayhead

You should remove the asterick and put log_id or 1 in the aggregate function for improved performance:

Code:

SELECT *,(SELECT COUNT(1) FROM history WHERE record='1234') AS total FROM history WHERE record='1234' ORDER BY date DESC LIMIT 1

That and make sure the record field has and index and that the index is being used.