[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Implied table sort?

Posted on 2008-11-12
9
Medium Priority
?
221 Views
Last Modified: 2012-05-05
[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
0
Comment
Question by:bamapie
  • 5
  • 3
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22940441
>>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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22940476
It should return in the order of the clustered index if no sort is specified.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22940516
That is not always the case...
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 60

Expert Comment

by:chapmandew
ID: 22940537
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
 

Author Comment

by:bamapie
ID: 22940539
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 22940567
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
 

Author Comment

by:bamapie
ID: 22940585
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22940613
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
 

Author Comment

by:bamapie
ID: 22940951
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question