Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227

    Unanswered: order by because of distinct

    I have a strange situation (I think). Within a view (which explains why I use the top 100% --> to use an order by) I have this query:
    Code:
    select TOP 100 PERCENT * 
    from tbOfferDetails     	
    where ofd_id = ofd_parent and ofd_fk_off_id = 100
    This gives me the following results:
    ofd_fk_off_id off_fk_class_id
    100 2753
    100 2753
    100 2071
    100 2753

    Now I change the query to:
    Code:
    select distinct ofd_fk_off_id, ofd_fk_class_id from
      (select TOP 100 PERCENT * 
      from tbOfferDetails       	
      where ofd_id = ofd_parent and ofd_fk_off_id = 100
      order by ofd_sequence ASC ) as tbOffers
    This gives me the following results:
    ofd_fk_off_id off_fk_class_id
    100 2071
    100 2753

    In the execution plan, it says that a distinct order by is used on off_fk_class_id. My question is: why is this done? I want only a distinct and not an order by. So is there a way to change this (default?) behaviour.
    Johan

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: order by because of distinct

    First of all

    Why are you doing a

    Select Top 100 PERCENT

    you want all the rows

    So a simple select would do

    select distinct ofd_fk_off_id, ofd_fk_class_id from
    (select *
    from tbOfferDetails
    where ofd_id = ofd_parent and ofd_fk_off_id = 100
    order by ofd_sequence ASC ) as tbOffers


    Second

    The results are OK
    in your second example your doing an ORDER BY
    that's why you don't have the rows in the same order

  3. #3
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    I use the top because the select statement is within a view and otherwise I can not use the order by.

    For the results of the second query: they need to be in the same order as the first one, so first 2753 and then 2071. But because of the "select distinct" in the second query it not only performs a distinct but again an "order by" which I did not specify in my query. So my question is why it performs the "order by" and how I can prevent it.
    Johan

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    it's doing an order by to bring all the duplicates together
    to then eliminate them

  5. #5
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: order by because of distinct

    You'll have to do a second "top 100 percent / order by"
    on the Offers to get the wanted order

  6. #6
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Unfortunately, it's not possible to perform an extra order by, because the only correct order is using the field "ofd_sequence" and if I add it to the second query I get the results of the first one. Can't I use another statement. I tried changing the clustered index, but this didn't work either. Someone with an idea?
    Johan

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    OK

    1- What do you have in your tables
    2- What result do you want


    3- We'll find something that works
    Enigma in online !

  8. #8
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    1. OK, I have three tables: tbOffers (off_id, price and some other fields) and tbOfferDetails (ofd_id, ofd_fk_off_id, ofd_fk_class_id) which are self explaining I think. Every record from the tbOfferDetails table has a foreign key to the third table tbClassifications (class_id, class_caption and some other fields) and to the table tbOffers. Order details are added and have a mandatory sequence. Within one order, a certain class_id (from tbClassifications) can occur multiple times.

    2. What I want: all ofd_fk_class_id values for a certain offer (field ofd_fk_off_id in the order details table), but only one time and in the order of the sequence field "ofd_sequence" from the table tbOfferDetails.
    Johan

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    This must not be it but maybe it will give some ideas

    Select Offers.IdOffer, ClassDetails.IdClass
    From Offers
    Inner Join (Select Distinct Top 100 Percent Details.IdOffer, Details.IdClassification,
    From Classifications
    Inner Join Details
    On Details.IdClass = Classifications.IdClass
    Order By Details.Id Asc) ClassDetails
    On ClassDetails.IdOffer = Offers.IDOffer


    ???????
    Can you give some data examples

  10. #10
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Sorry, does not get the desired result. Below is some data. Maybe this will help.

    tbClassifications:
    class_id class_caption
    2071 'Caption_2071'
    2753 'Caption_2753'

    tbOffers:
    off_id off_date
    100 '01/01/2004'

    tbOfferDetails
    ofd_id ofd_fk_off_id ofd_fk_class_id ofd_sequence ofd_description
    1 100 2753 100 'First line'
    2 100 2753 200 'Second line'
    3 100 2753 300 'Third line'
    4 100 2753 400 'Fourth line'
    5 100 2071 500 'Fifth line'
    6 100 2753 600 'Last line'
    Johan

  11. #11
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    and what result would you like ?

  12. #12
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    what I wrote in my first post, so

    ofd_fk_off_id ofd_fk_class_id
    100 2753
    100 2071
    Johan

  13. #13
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Select Distinct Top 100 PERCENT Details.IdClass,Details.IdOffer,DetailsOrder.MinId
    From Details
    Inner Join (Select Min(Id) as MinId,Idclass
    From Details
    Group By IdClass) DetailsOrder
    On DetailsOrder.IdClass = Details.IdClass
    Order By DetailsOrder.MinId


    Where
    Classes = tbClassifications
    Details = tbOfferDetails
    Offers = tbOffers

  14. #14
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Finally, it works. I just changed a little bit from the last post of Karolyn. Because it's a view, a distinct was not possible so made an extra subquery of it. Anyway, thanks a lot Karolyn.

    Code:
    select ofd_fk_off_id, ofd_fk_class_id from
    (select distinct top 100 percent tbOfferDetails.ofd_fk_class_id, tbOfferDetails.ofd_fk_off_id, minID
    from tbOfferDetails inner join
      (select min(ofd_sequence) as minID, ofd_fk_class_id
      from tbOfferDetails
      group by ofd_fk_class_id) DetailsOrder
    on DetailsOrder.ofd_fk_class_id = tbOfferDetails.ofd_fk_class_id
    order by DetailsOrder.minID) as Results
    Johan

  15. #15
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Was fun to do
    but as usual I'm slow on the starting blocks
    thanks to you too, i've learned on limitations of views

Posting Permissions

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