MySQL

need help with DISTINCT

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2005

Posts: 39

Reputation: nephish is an unknown quantity at this point

Solved Threads: 0

nephish

Offline

Light Poster

need help with DISTINCT

Jul 28th, 2005

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,

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT DISTINCT UnitNumber FROM table ORDER BY `autoinc` DESC
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?

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT DISTINCT UnitNumber, value1, value2 FROM table ORDER BY autoinc desc
select distinct UnitNumber, value1, value2 from table order by autoinc desc

or is there a better way?
thanks

•

Join Date: Jun 2005

Posts: 354

Reputation: Troy is an unknown quantity at this point

Solved Threads: 5

Troy

Offline

Posting Whiz

Re: need help with DISTINCT

Jul 28th, 2005

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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT UnitNumber, value1, value2
FROM table
WHERE UnitNumber IN (SELECT DISTINCT UnitNumber
  FROM table
  ORDER BY autoinc desc)
ORDER BY UnitNumber
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.

Troy Wolf is the author of SnippetEdit. "Website editing as easy as it gets." IX Web Hosting

•

Join Date: Jun 2005

Posts: 39

Reputation: nephish is an unknown quantity at this point

Solved Threads: 0

nephish

Offline

Light Poster

Re: need help with DISTINCT

Jul 28th, 2005

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!