Repeated Value

Hi I have a query that displays 5 column but I would like to supress the repeated values from one of them...

like this

__Event___|___Reference___|___Name___|____Date____ _|
________1_|__AB01000005__|__Diogo____|__01/01/2005_|
________2_|__AB01000005__|__Diogo____|__02/01/2005_|
________3_|__AB01000005__|__Diogo____|__03/01/2005_|
________4_|__AB01000001__|__MAria____|__04/01/2005_|
________5_|__AB01000002__|__Joao_____|__05/01/2005_|

this table is what I am getting
I want it to return this

__Event___|__Reference____|__Name____|___Date_____ __|
_______3__|__AB01000005__|__Diogo____|___03/01/2005_|
_______4__|__AB01000001__|__Maria____|___04/01/2005_|
_______5__|__AB01000002__|__Joao____|____05/01/2005_|

This way I only get the latest reference....

There is any way to do this?select Event
, Reference
, Name
, theDate
from yourtable as t
where theDate
= ( select max(theDate)
from yourtable
where Reference = t.Reference )|||That does not seem a good solution since I am using 3 tables to get the values
if you have another ideia I would apreciate|||What is the difference between this problem and your other one entitled "Can't Use Distinct Here"?|||guess this one is less detailed....

Oh if you don't mind... I have 2 tables... one as all the references as text, and the other only have the used ones....

I wantes to query all of the references that are not being used...

I think this one is easy but I am a newbie at SQL!!!

guess you can tell me how to do it :)|||guess you can tell me how to do it :)actually, we would prefer that you do a little bit of this work yourself ;)

hint: left outer join|||I've been trying something like this

SELECT Badges.BadgeReference
FROM Badges, Visitors
WHERE (((Badges.BadgeReference) Like Not[Visitors].[VisitorReference]));

And it does not work
then I tryied

SELECT Badges.BadgeReference
FROM Badges LEFT JOIN Visitors ON Badges.BadgeReference = Visitors.VisitorReference
WHERE (((Badges.BadgeReference) Like Not [Visitors].[VisitorReference]));

Nothing... and finally

SELECT Badges.BadgeReference
FROM Badges Right JOIN Visitors ON Badges.BadgeReference = Visitors.VisitorReference
WHERE (((Badges.BadgeReference) Like Not [Visitors].[VisitorReference]));|||oooooohh... forget... outer joins... I'll be trying
hehehe|||ok guess I didn't make it....
:( what a failure!!!!|||...

I can only get the ones that I am using and not the inverse... :(|||keep looking , you are almost there

what are you using as a reference for left outer join?

textbook? tutorial web site?|||I'm trying by myself, and searching for something on the internet...|||SELECT Badges.BadgeReference
FROM Badges Left Outer Join Visitors On Badges.BadgeReference = Visitors.VisitorReference
WHERE Badges.BadgeReference <> Visitors.VIsitorReference

I've reached here...... It returns nothing... it was suposed to return 1,2,3 and 4, the 5 was being used...

if I take off the where it shows all the references including the 5 and I don't want it to do it....

Getting desperated with this one|||you need a good SQL tutorial

there are several on my SQL Links (http://r937.com/sqllinks.cfm) page

meanwhile, in a LEFT OUTER JOIN, rows from the left table which have no matching row in the right table are still returned in the results, but the columns from the right table are all null

therefore if all you want is the unmatched rows,

SELECT Badges.BadgeReference
FROM Badges Left Outer Join Visitors
On Badges.BadgeReference = Visitors.VisitorReference
WHERE Visitors.VIsitorReference is null

:)