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:

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:

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.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|||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.|||it's doing an order by to bring all the duplicates together
to then eliminate them|||You'll have to do a second "top 100 percent / order by"
on the Offers to get the wanted order|||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?|||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 !|||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.|||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|||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'|||and what result would you like ?|||what I wrote in my first post, so

ofd_fk_off_id ofd_fk_class_id
100 2753
100 2071|||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|||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.

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|||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