removing duplicate entries

[wickdbob]

Hi
I have the following query which displays records based on a certain timeframe sorted by 'in' variable:
$query = "SELECT DISTINCT * FROM data WHERE out >= '$time' ORDER BY 'in' DESC";
The problem is that it duplicates entries on the 'user' variable which I would like to restrict to one entry only and the latest one recorded in the database.
Is there a way of doing this easily??
Appreciate any help.
BoB.

 

[swampBoogie]

You need a correlated sub-query for that which is not supported in Mysql.

Yoy can do it with a temp table though

insert into temp(username,`out`)
select max(`out`), username from data
group by username

select data.* from data inner join temp on
data.username = temp.username
and data.`out` = temp.`out`
order by `in` desc

Why don't you rename the in and out columns to something that is not reserved in SQL like timeIn timeOut?

 

[wickdbob]

Hmmm.. so there is no other way of doing this other than using another table?? I cannot utilise GROUP BY or DISTINCT in any way?