Post reply

One instance

  • March 5, 2002 at 2:19 pm

    #77796

    I have joined two tables that have GUID as the common field,, each person have at least one record in this table. when i do a simple join i get more records when someone has more than one entry in any of these two tables.

    How would i just get one record from these tables? I dont want duplicates from either table...... i tried distinct but it did not work?

    Please post something asap thanks.

  • March 5, 2002 at 3:00 pm

    #424921

    Ok first try this and then I will get more complicated it does not meet your needs. DISTINCT is for all columns in your select statement so if you reference more columns than you need you will get duplicates in some columns becuase one of the columns has no duplicate. Try getting rid of all columns you don't need and try again. If this does not work is there a date field in you tables or some other field with a high and low value if so see my FAQ post at http://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=3&faqid=140 titled,

    quote:

    Can I eliminate duplicates without deleting any records?

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • March 5, 2002 at 3:26 pm

    #424924

    ok,, lets change the requirement a little bit

    instead of two tables lets use one but include date columns

    so i have guid and date

    every on has at least one record and some more

    what i want is just one record for each person and IF there is more than one record for any person i want the one with most recent date.

    Thanks

  • March 5, 2002 at 3:37 pm

    #424926

    select person, max( date)

    from table

    group by person

    Steve Jones

    steve@dkranch.net

  • March 5, 2002 at 3:57 pm

    #424935

    thanks Steve,, i am so dumb 🙁

Viewing 5 posts - 1 through 4 (of 4 total)

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