Results 1 to 14 of 14

Thread: Repeated Value

  1. #1
    Join Date
    Jan 2005
    Posts
    23

    Question Unanswered: 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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select Event
         , Reference
         , Name
         , theDate
      from yourtable as t
     where theDate
         = ( select max(theDate)
               from yourtable
              where Reference = t.Reference )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    23
    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

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What is the difference between this problem and your other one entitled "Can't Use Distinct Here"?

  5. #5
    Join Date
    Jan 2005
    Posts
    23
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by santal_maluko
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2005
    Posts
    23
    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]));

  8. #8
    Join Date
    Jan 2005
    Posts
    23
    oooooohh.... forget.... outer joins... I'll be trying
    hehehe

  9. #9
    Join Date
    Jan 2005
    Posts
    23
    ok guess I didn't make it.....
    what a failure!!!!

  10. #10
    Join Date
    Jan 2005
    Posts
    23
    ...

    I can only get the ones that I am using and not the inverse...

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    keep looking , you are almost there

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

    textbook? tutorial web site?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2005
    Posts
    23
    I'm trying by myself, and searching for something on the internet...

  13. #13
    Join Date
    Jan 2005
    Posts
    23
    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

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need a good SQL tutorial

    there are several on my SQL Links 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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •