Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

removing duplicate entries

Status
Not open for further replies.

wickdbob

Technical User
Jan 26, 2003
2
NZ
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.
 
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?
 
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top