DISTINCT keyword in SQL statement - how to...

Results 1 to 7 of 7

Thread: DISTINCT keyword in SQL statement - how to...

  1. #1
    Perini14 Guest

    Default DISTINCT keyword in SQL statement - how to...

    How to get recordset without duplicates in just 2 columns? Lets say that I have a table with addresses (firstname, lastname, address, city...). If I use this statement:<BR><BR>select distinct firstname, lastname from table1<BR><BR>then in recordset I can&#039;t access the address or city field!! But, if I include address and city in sql statement, then the distinct command will affect the other fields, and I will have records with duplicate firstname and lastname.<BR>I&#039;ve tryed something like this:<BR><BR>select (distinct firstname, lastname), address, city from table1<BR><BR>but that doesn&#039;t work!<BR><BR>So, how do I do it?<BR><BR>Thanks!<BR>

  2. #2
    JAT Guest

    Default RE: DISTINCT keyword in SQL statement - how to...

    My question is, why would you want to do that? If you have same first and last name but a different address, wouldnt they still be distinct records? What is your desired result?

  3. #3
    Perini14 Guest

    Default PLEASE HELP

    Nevermind why! The problem is much more complicated and I need to do it!<BR><BR>Anybody... help!

  4. #4
    Join Date
    Dec 1969

    Default RE: DISTINCT keyword in SQL statement - how to...

    If you have two records<BR><BR>jill basset address1<BR>jill basset address2<BR><BR>if you were to select distinct as you desire, how can you be sure which address you&#039;ll get? What if one time it is address1 and another time it is address2? Does it make a difference?

  5. #5
    JAT Guest

    Default RE: PLEASE HELP

    In that case I suppose you could do <BR><BR>select firstname, lastname, address, city<BR>from tablename<BR>where (firstname, lastname) in (select distinct firstname, lastname from tablename)<BR><BR>Of course I dont have a db to test this on and it may very well return the exact same results as before not to mention this is using SQL2 and you may not be able to support it as constructed...but without more info I couldnt begin to detect what your desired output is so its all ive got to offer

  6. #6
    Perini14 Guest

    Default Syntax error !!!

    I&#039;ve tested your string, and I get a syntax error. Is there another way?<BR>Ok, lets make things clear, what&#039;s the output I need:<BR>Let&#039;s say that my address table looks like this:<BR><BR>John Smith Address1<BR>John Smith Address2<BR>Ana Smith Address3<BR>John Smith Address4<BR><BR>The output I want is:<BR><BR>John Smith (any address is fine)<BR>Ana Smith (any address if fine)<BR><BR>Anybody know how to do it ???<BR>

  7. #7
    Perini14 Guest

    Default RE: DISTINCT keyword in SQL statement - how to...

    See the "Syntax error!!!" post !<BR>

Posting Permissions

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