Post reply

Sql help with duplicates?

  • September 25, 2006 at 10:19 am

    #174169

    Hi,

    Not sure if this is the right place for this thread.....

    I have just started SQL, and would appreciate some help..... i have the following code:

    SELECT

        tblSkillText."SkillText",

        tblSkillLevelValue."PassportID", tblSkillLevelValue."SkillLevelID", tblSkillLevelValue."CompletedAt", tblSkillLevelValue."Active", tblSkillLevelValue."PctCompleted"

    FROM

        { oj (("Passports2"."dbo"."tblSkillLevelText" tblSkillLevelText INNER JOIN "Passports2"."dbo"."tblSkillText" tblSkillText ON

            tblSkillLevelText."LanguageID" = tblSkillText."LanguageID")

         INNER JOIN "Passports2"."dbo"."tblSkillLevel" tblSkillLevel ON

            tblSkillLevelText."SkillLevelID" = tblSkillLevel."SkillLevelID")

         INNER JOIN "Passports2"."dbo"."tblSkillLevelValue" tblSkillLevelValue ON

            tblSkillLevel."SkillLevelID" = tblSkillLevelValue."SkillLevelID"}   

    WHERE

        tblSkillText."SkillText" ='operate a computer' and tblSkillLevelValue."SkillLevelID" <6

    It has churned out over 100,000 rows. The question or help i need is the variable: tblSkillLevelValue."PassportID" where it is like a membership number, and is repeated.... how can i get this variable to result in just one line where tblSkillLevelValue."CompletedAt" (it is in time format) to be used at the latest set:

    eg:

    passport id, date

    23315 21/09/06

    23315 22/09/06

    23315 26/09/06

    So i need the result to choose only 26/09/06 and eliminate the other 2 as the 26/09/06 is the latest date. Is this possible?

    Thanks

    Jay

  • September 26, 2006 at 11:36 am

    #662547

    Can you post schema, sample data, and desired output?

  • September 28, 2006 at 7:53 am

    #662984

    On a random without further information...

     

     

    delete a

    from table a

    join table a1

    on a.passportid=a1.passportid

    where

    a.date > a1.date

     

    That should leave you with only 1 passport record and only the highest date.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply