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?

Who is Participating?
chapmandewConnect With a Mentor Commented:
to sort in a view (if you really think you have to)

create view myview
select top 100 percent fieldnames
from table
order by somefieldname
>>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.
It should return in the order of the clustered index if no sort is specified.
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

That is not always the case...
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
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.
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.
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.