Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2003
    Location
    California
    Posts
    40

    Unanswered: DISTINCT() Question

    Hello,

    I currently have a SQL Statement:
    SELECT ID, SignDescription FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription

    However, it shows several duplicates (which is expected, i do have several duplicates in the database), but I don't want duplicates, so I tried the following:

    SELECT DISTINCT(SignDescription), ID FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription

    This shows the same exact results, which if I understand right, is expected also - my ID fields are all unique so of course this is going to happen.

    If I do this (omit the ID field from the results):
    SELECT DISTINCT(SignDescription) FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription

    I get all my unique signs with a unique SignDescription, but then I don't have the ID field...which I need.
    But the problem is I need the ID field returned in the same results/recordset/whateveryoucallit.
    Is this even possible?

    thanks,
    Jamie

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it's possible, but you need to change the specs



    in english, what do you actually want to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    Ok,

    I was worried that was a little confusing.

    What I want to do is return all records in my db that have unique data in the signdescription field, and I want to use only the id field and the signdescription field. (and then of course there is the rest of the statement - WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription...but that part is already working for me)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    bear with me and let's go back and approach this thing from basic principles rather than from trying to browbeat the sql into shape, because until you understand the problem, writing sql is futile

    suppose you have three rows, all with the same signdescription

    which row do you want to keep? describe it in words
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    k.
    one row.

  6. #6
    Join Date
    Sep 2002
    Posts
    53
    select MAX(ID) as ID, SignDescription FROM Signs Group by SignDescription Order BY ID

    or

    Select MIN(ID) as ID, SignDescription FROM Signs Group by SignDescription Order BY ID

    You can use this method if it doesn't matter which ID out of the duplicates you pull back. If it does matter which ID out of the duplicates you pull back then you need to reconsider the design of the table; which IMO is the best way to go.
    KDK

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by domiflichi
    k.
    one row.
    You funny...which one

    1 X
    2 X
    3 X

    ???


    Read the hint sticky at the top of the thread
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    KrustyDeKlown, it does not matter which id I pull, so I will try your example.

    Brett, it doesn't matter which one, so 1 or 2 or 3 would work just the same for me. Thank you for being precise, I know you don't want to give a wrong answer. Which part of the hint sticky do I need to apply, (besides stating my question better) if any? (I did read it though...before my original post even)

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sample data, expected results, DDL

    That part
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by domiflichi
    Thank you for being precise, I know you don't want to give a wrong answer.
    Hey, I'm all for giving wong answers
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by domiflichi
    k.
    one row.
    does it have to be a consistent row, or can it be a conglomeration

    for example, suppose you have the following "duplicate" rows

    4 fred 102 aaa y
    5 fred 104 bbb x
    6 fred 105 ccc z

    there are three "fred" rows and you want to end up with just one

    "yeah," i can hear you saying, "that's right, just one!"

    would you accept this --

    5 fred 102 ccc x

    if so, then the sql is easy, but if not, then why not? because there never was a row like that!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    First off, I want to tell Brett that I didn't necessarily mean what I said about giving wrong answers. Actually I thought you and r937 were the same (so really I guess I should thank r937 for starting off being precise...but you too Brett )- I didn't notice there were 2 people replying to me. Additionally, I didn't mean that one of you would give me a 'wrong' answer, I just meant, that you guys were really trying to understand my situation as to not give me something that didn't apply to me...or work for my situation.

    Next, I'm not sure if I understood at first what Krusty meant when he said I can use his method if it doesn't matter which id I pull from the duplicates...is he saying that things are going to be mixed up, similar to what you are asking r937? If so, I guess it's acceptable in my current situation, as I'm not actually using the IDs right now (I just have to supply an 'id' to a function because it is a required parameter...but that's a separate subject). However, in the future, I might need to actually use that ID, which would mean that it would actually have to be 'correct'. Which brings me to my next question - what do you mean by

    then why not? because there never was a row like that!!!
    ? I'm not sure I understand.

    K, as far as the sample data, expected results, etc...if you guys want, I can give you more specific information. I just didn't realize that this was going to be this complicated when I first started this post. But I guess part of that is my fault for not being perfectly clear.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, here's one quick way to generate something useful

    select SignDescription, max(X), max(Y), ... max(Z)
    from thetable
    group by SignDescription

    where X, Y, Z are the other columns in the table

    will that work?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    I think so. That's pretty much what I have now from what Krusty suggested. Here's my full SQL statement:

    Code:
    SELECT MAX(ID) AS ID, SignDescription FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' GROUP BY SignDescription ORDER BY SignDescription
    So this will work as long as it returns the correct IDs I guess. It seems to be working great, although I haven't checked if it is giving the right IDs yet.

Posting Permissions

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