Implied table sort?

[Sql Server 2005]

I have a view that returns certain records that were added over the past n minutes.

The sorting for these returned records isn't ideal.  And of course, I can't sort in a view.

I wish the underlying table I'm querying from had an "implied sort", either by its identity ID field, or by a certain date/time field.

Is there any way I can get my returned records in this view to have an innate sort already applied to them?

Thanks
bamapieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
>>And of course, I can't sort in a view

Actually, you can, but not sure why you'd need to.

You can NEVER ensure a sort order unless you use ORDER BY.
0
BrandonGalderisiCommented:
It should return in the order of the clustered index if no sort is specified.
0
chapmandewCommented:
That is not always the case...
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

chapmandewCommented:
for example:


drop table #t
create table #t(idfield int identity(1,1),  valfield char(1))

insert into #t(valfield)
select 'A'
union all
select 'B'
union all
select 'C'
union all
select 'D'
union all
select 'E'
union all
select 'F'

create clustered index idx_something_temp on #t(idfield asc)

select * from #t  --returns in the order expected

alter table #t
add constraint u_something unique(valfield desc)

select * from #t  --different order
0
bamapieAuthor Commented:
How *would* I sort in my view?

I do have a clustered index specified--my primary key, an identity field--but I'm honestly not getting records returned that sort by that field.
0
chapmandewCommented:
to sort in a view (if you really think you have to)

create view myview
as
select top 100 percent fieldnames
from table
order by somefieldname
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bamapieAuthor Commented:
Sorry, chapmandew, we had some crosstalk there and I hadn't seen your post by the time I posted.

Let me try your temp-table route later and see.

Thank you.
0
chapmandewCommented:
Yeah, sorry.  That really wasn't in terms of your problem....just showing that a clustered index doesn't ensure sorting based on the key.
0
bamapieAuthor Commented:
Okay, tell me, why is the idea of sorting in a view so anathema?

Basically, in my situation, I have a table of readings--factory temperature, automated raw material deliveries, etc.  The table contains data from different factories, different production lines, different transaction types, different materials.

My view would be a selection of only certain of those variables.  An SP isn't possible only because we're interfacing with a 3rd-party product that can only connect to tables or views.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.