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

    Unanswered: Help with SELECT and DISTINCT

    Hello,

    I have the following statement:
    Code:
    SELECT Orders.PropID PropertyLocations.PropertyLocation, PropertyLocations.DistanceToProp, PropertyLocations.StreetAddress1, PropertyLocations.StreetAddress2, PropertyLocations.City, PropertyLocations.State, PropertyLocations.Zip, Orders.ID, Orders.WorkOrderNum, Orders.PropID FROM PropertyLocations INNER JOIN Orders ON PropertyLocations.ID = Orders.PropID WHERE (Orders.IsCompleted = 0) AND (Orders.OrderStatus = 'Ready To Deliver' OR Orders.OrderStatus = 'Ready To Install' OR Orders.OrderStatus = 'Install Incomplete') AND WorkOrderNum IN (" & selectedOrderList & ") ORDER BY PropertyLocations.DistanceToProp
    which works fine, except that I get duplicates of Orders.PropID. I do not want this. So what I tried was this:

    Code:
    SELECT DISTINCT Orders.PropID PropertyLocations.PropertyLocation, PropertyLocations.DistanceToProp, PropertyLocations.StreetAddress1, PropertyLocations.StreetAddress2, PropertyLocations.City, PropertyLocations.State, PropertyLocations.Zip, Orders.ID, Orders.WorkOrderNum, Orders.PropID FROM PropertyLocations INNER JOIN Orders ON PropertyLocations.ID = Orders.PropID WHERE (Orders.IsCompleted = 0) AND (Orders.OrderStatus = 'Ready To Deliver' OR Orders.OrderStatus = 'Ready To Install' OR Orders.OrderStatus = 'Install Incomplete') AND WorkOrderNum IN (" & selectedOrderList & ") ORDER BY PropertyLocations.DistanceToProp
    which gives me the exact same results. I don't understand, I thought that DISTINCT would give me only unique results???

    So for example, here is a set of results: (forgive me if I'm giving more than what's needed)

    Code:
    Orders.PropID                Orders.ID                     Orders.WorkOrderNum
    37                               4179                           8345
    241                             4181                           8462
    145                              4156                          8313
    145                             4162                           8316
    82                               4201                           8489
    So I get the above results running either select statement above.

    The Orders.ID and Orders.WorkOrderNum fields will never be the same, but as you can see, the Orders.PropID fields may be the same, and this is undesirable - I want to only retrieve unique ones.

    What I'm trying to do (if it even helps or matters to anyone) is, build up a Google driving directions link using the results. The PropID represents a building location, so I don't want to have the same building location show up twice as that would be silly giving it to Google twice.

    Oh yeah, the database is MSDE 2000.

    Thank you much.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    DISTINCT returns unique rows. All those rows are unique. We know what you get, and we know what you don't want. But we don't know what you actually do want. Can you show what you want instead of this:
    Code:
    Orders.PropID                Orders.ID                     Orders.WorkOrderNum
    37                               4179                           8345
    241                             4181                           8462
    145                              4156                          8313
    145                             4162                           8316
    82                             4201                           8489
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The DISTINCT qualifier means to filter out rows which are a complete duplicate of another row. The DISTINCT qualifier applys only to rows, it doesn't address individual columns unless only one column is in the result set.

    I'd suggest that you change the SELECT DISTINCT to only use the one column where you want distinct values.

    -PatP

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    i will never understand why some people feel the need to code all their SQL on a single, humoungously long line... it makes the SQL ~so~ hard to read... but anyhow... if you want only one result row for each PropID, then obviously you cannot show order details (Orders.ID, Orders.WorkOrderNum), because a property can have more than one order -- you could, instead, count the orders for the property, or some other aggregate of the orders data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    My bad,

    This is what I want:
    Code:
    Orders.PropID                Orders.ID                     Orders.WorkOrderNum
    37                               4179                           8345
    241                             4181                           8462
    145                              4156                          8313
    82                               4201                           8489
    or

    Code:
    Orders.PropID                Orders.ID                     Orders.WorkOrderNum
    37                               4179                           8345
    241                             4181                           8462
    145                             4162                           8316
    82                               4201                           8489
    ...I don't really care which one.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the SQL gets quite a bit more complex if you want only one order for each property

    why do you need only one? if there is more than one, won't showing just one of them be slightly deceptive?

    in any case, SQL has no provision for choosing the "i don't care which" option

    please explain which one you want -- the earliest one? the one with the longest workorder id? it will have to be something involving a MIN or MAX function, and it will require a subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2008
    Posts
    135
    try like this
    Code:
    SELECT DISTINCT 
    	p.PropertyLocation,
    	p.DistanceToProp, 
    	p.StreetAddress1, 
    	p.StreetAddress2, 
    	p.City, 
    	p.State,
    	p.Zip, 
    	o.ID,
    	o.WorkOrderNum, 
    	o.PropID
    FROM 
    	PropertyLocations AS P
    INNER JOIN 
    	(SELECT MIN(ID)AS ID,WorkOrderNum,PropID FROM Orders GROUP BY WorkOrderNum,PropID)AS o ON p.ID = o.PropID 
    WHERE (o.IsCompleted = 0) 
    		AND (o.OrderStatus = 'Ready To Deliver' 
    		OR o.OrderStatus = 'Ready To Install' 
    		OR o.OrderStatus = 'Install Incomplete')
    		AND WorkOrderNum IN (" & selectedOrderList & ") 
    ORDER BY p.DistanceToProp

  8. #8
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    Sorry for the late response everyone.

    r937 - I only need one, because the select statement is building a dynamic Google Directions link for our sign installers, and it would be pointless for me to include the same property (which is a physical building with a specific address) more than once in the link, so it's not really deceptive. The installers will have all the paperwork in hand, so they will know if there is more than 1 order for any given property.
    As for which one I want, I'll take any of those that you suggest - the one with the largest ID, longest work order number, sure...

    which brings me to bklr's post - I think that's what I want, but I'm getting an error when trying to run it:
    Code:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'IsCompleted'.
    so I don't know for sure if it will work for me, and since I'm not an expert, I can't tell just by looking at the statement to know if that's what i want...hence turning to you experts on this forum.

    Thanks for all the help so far everyone.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by domiflichi
    The installers will have all the paperwork in hand, so they will know if there is more than 1 order for any given property.
    oh, really? in that case, you don't need to show any order

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

  10. #10
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    But they still need to know how to get there. Hence my sql statement trying to select unique propertys to build a Google Directions link. I'm sorry if my naiveness is frustrating, but it seems like I'm so close...

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by domiflichi
    I'm sorry if my naiveness is frustrating, but it seems like I'm so close...
    not a problem

    Code:
    SELECT DISTINCT 
           PropertyLocations.ID
         , PropertyLocations.PropertyLocation
         , PropertyLocations.DistanceToProp
         , PropertyLocations.StreetAddress1
         , PropertyLocations.StreetAddress2
         , PropertyLocations.City
         , PropertyLocations.State
         , PropertyLocations.Zip
      FROM Orders
    INNER 
      JOIN PropertyLocations  
        ON PropertyLocations.ID = Orders.PropID
     WHERE Orders.IsCompleted = 0
       AND Orders.OrderStatus IN ( 'Ready To Deliver' 
                                 , 'Ready To Install' 
                                 , 'Install Incomplete' ) 
       AND Orders.WorkOrderNum IN (" & selectedOrderList & ") 
    ORDER 
        BY PropertyLocations.DistanceToProp
    notice that the restrictions are all applied to the Orders table in the WHERE clause -- it is the selection of specific orders based on those criteria that determine which properties to list

    but there is no need to list any of the orders info

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

  12. #12
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    I think that's it. It seems to work!

    I'm trying to understand why/how it works, and your explanation helps a little, but it still hurts my head.

    I was looking at your book sounds like just what I need...I think I might have to buy that pretty soon...it seems that I need to learn more.

    Thanks for all your (and everyone else's) help!

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by domiflichi
    I was looking at your book sounds like just what I need...I think I might have to buy that pretty soon...it seems that I need to learn more.
    you can read chapter 3 here --> Simply SQL: The FROM Clause [Server Side Essentials]
    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
  •