need help with DISTINCT

[nephish]

Hey there,
i have a table in MySQL that is updated about every 5 minutes with about a hundred new records. For an application i am building, i only need the info from the most recent entry for each specific unit. But, i need all of the info in that row.

i was going to use DISTINCT like this,

SELECT DISTINCT UnitNumber FROM table ORDER BY `autoinc` DESC

it gives me only the last record for each unit, but only returns the UnitNumber and i need all of the colums for each UnitNumber that it gives me.

or if i use something like this, will it only pull out items that have only one entry of each?

select distinct UnitNumber, value1, value2 from table order by autoinc desc

or is there a better way?
thanks

[Troy]

You could use a subquery, but subqueries are not supported in MySQL until 4.1. Many hosting companes are running 3.2, 3.3, or 3.4. What version of MySQL do you have? (PHP's phpinfo() command will tell you--if you use PHP.)

To use a subquery to solve your issue, do this:

select UnitNumber, value1, value2
from table
where UnitNumber in (select DISTINCT UnitNumber
  from table
  order by autoinc desc)
order by UnitNumber

Without subqueries, you just simply do 2 queries. The first queries distinct UnitNumbers, then you run a second query to pull all rows matching those UnitNumbers--or however your tables are keyed.

However, might I suggest instead you create a table that only contains 1 row for each of your distinct items. Then code your app to insert to your main table and always update this new table. This way, you have a table that always contains a simple list of the most recent rows--1 for each UnitNumber. I don't know your situation or your app, so maybe this is not a good option.

[nephish]

i am using sql 4.024 (debian-stable) so i gues the subqueries are out. i like the idea of writing two queries, that would work i think. i am coding in python and have one table with current info, another table of almost a million records (grows by about 50000/day) that i am using to query to keep the current status updated. i run this every 5 minutes or so. so i created a buffer table of just the last 5 minutes worth of entries, using that to run the queries, check for matches in UnitNumbers and update the current table accordingly, that way the web server isn't having to query a million record table to get the most recent info on the unit.
thanks, i am closer now than i was yesterday!