View Order Dissappearing

Dec 15, 2006

When I create a view in SQL and include an ORDER BY clause i can see it
in Management Studio. However, when I call the same view from an ASP
page the order goes completely haywire.


Any ideas?

View 24 Replies


ADVERTISEMENT

Order By Clause In View Doesn't Order.

May 18, 2006

I have created view by jaoining two table and have order by clause.

The sql generated is as follows

SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth



If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

View 16 Replies View Related

ORDER BY In A View

Jan 14, 2008

I have a view in SQL SERVER 2005 which includes ORDER BY in my SQL expression.
When I press "execute", the results of the view come out ordered by the selected field, as they should be.
When I close the view and choose "Open View", the results come out not ordered. The same thing happens when I'm trying to show the results in a GridView.
Why does it happen?

View 2 Replies View Related

Order By In View

Feb 7, 2006

I have to provide the Order by Value from Front End (VB) to the DB , so that I can receive the Crystal Report in the order that I have given.
thanks in advance... :confused:

View 5 Replies View Related

ORDER BY Can Be Used In View?

Feb 21, 2006

In MSSql,"Order by" and "distinct" can be used in view?

thanks!

View 4 Replies View Related

Order By In A View

Feb 6, 2004

i m required to use order by in a view, how to do it, simple order by doesn't work for it, any other way,i have heard there is one in oracle, but wat abt SQL Server???:confused:

View 2 Replies View Related

Please Help With Order By In A View

Jan 11, 2007

I have make a view where i have a order by
when i execute my view in modify my result are allright
but when i say open view to see my view it is not
in order by.

I use the view in a asp site to the internet and i can't use order by here
because the field i orderby shall not come up in my site, i only use this field
to order by



Hope someone can help



Alvin

View 9 Replies View Related

Order By Problem In View

Apr 29, 2006

I have downloaded and installed SQL Server Express 2003 Adv.. I have created a new database in it and also a table called Customers. In that I have created one field called CustomerName as nvar(255). I have made 10 records by filling in the field with various names. I have then created a new view and asked it to order the records in descending order. When I save and show the view it shows the records still in the order that I inserted them not in descending alphabetical order. If I make it show in ascending order the same problem still occurs. Collation settings are correct for the computer at Latin1. However, I have tried a few others, but none make any difference. SQL Server Express 2003's view will not list my Customers in either ascending or decending alphabetical order. Can anyone give me any suggestions?

View 4 Replies View Related

ORDER BY In VIEW Not Working

Jan 24, 2006

I have the view below and if I use vwRouteReference as the rowsourcefor a combo box in an MS Access form or run "SELECT * FROMvwRouteReference" in SQL Query Analyzer, the rows don't come throughsorted by Numb.Everything I've read on the web suggests that including the TOPdirective should enable ORDERY BY in views. Does someone have an ideawhy the sorting is not working correctly for this particular view? thanks.CREATE VIEW vwRouteReferenceASSELECT TOP 100 PERCENT tblItem.ID,tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName,tblItem.Numb, tblQuestion.DescrPrimary AS TypeFROM tblItem INNER JOIN tblQuestionON (tblItem.ID = tblQuestion.Item_ID)WHERE (((tblItem.Category_ID)>0))UNIONSELECT TOP 100 PERCENT tblItem.ID,tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS FullName,tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS TypeFROM tblItemWHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR(tblItem.Type) = 'Route'))ORDER BY tblItem.Numb

View 49 Replies View Related

Order By Problem Within A View

Sep 21, 2007



Hi,

I have created a view which uses 3 tables, i also have a sort on one of the columns. However when I open the view the sort does not work. It does however sort the view correctly when executing the query within design view


Can anyone explain this or is it a bug within SQL Server Express 2005?


thanks

David

View 1 Replies View Related

Cannot Create A Order By View

May 22, 2006

Hello,

I am trying to create a simple view which uses self joins. I want the final result order by. I am able to create the view using Top clause followed by order by but when I simply query the view it does not appear in the correct order. Can some body help me with the issue.

CREATE VIEW [dbo].[vw_SalesRep_Chaining]

AS

SELECT Top 100 percent Rep_id AS RepId,

Rep_cd AS RepCode,

Region as Region,

CASE WHEN Market IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico') THEN 'Arizona / New Mexico'

WHEN Market IN ('L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'LA Metro'

WHEN Region = 'Western' AND Market NOT IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico', 'L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'Western - Other'

ELSE '' END AS Sub_Region,

RSM AS RSM,

UPPER(Regional_Manager) AS Regional_Manager,

Market,

Rep_Manager_id AS SalesManager,

UPPER(MarketManager) AS MarketManager,

New_Position AS NewPosition,

Rep_Status AS RepStatus,

UPPER(Rep_Payroll_name) AS PayrollName

FROM (SELECT SalesRep_GUID, Rep_id, Rep_cd, Rep_Payroll_name, Rep_SSN, Rep_Status, Hire_dt, Termination_dt, Commission_start_dt,

Rep_Manager_id, Region, Market, New_Position, Validity_start_dt, Validity_end_dt, Load_interval_id, Create_process_id, current_ind

FROM dbo.SalesRep

WHERE (current_ind = 1) AND (New_Position IN ('SC1', 'SC2', 'SC3', 'MSO')) AND (Region NOT IN ('Emerging Market')) OR

(current_ind = 1) AND (New_Position IS NULL) AND (Region IN ('TeleSales')) AND (Region NOT IN ('Emerging Market'))) AS A

INNER JOIN (SELECT Rep_id RepId, Rep_Payroll_name as MarketManager, Rep_Manager_id as RSM

FROM dbo.SalesRep AS SalesRep_2

WHERE (current_ind = 1)) AS B

ON A.Rep_Manager_id = B.Repid

INNER JOIN (SELECT Rep_id RepId1, Rep_Payroll_name as Regional_Manager FROM dbo.SalesRep AS SalesRep_1

WHERE (current_ind = 1)) AS C

ON B.RSM = C.Repid1

WHERE (A.Region IS NOT NULL)

Order by Rep_Id, Rep_Cd

This is the script I have used to create the view. But when is simply query the view it does not appear in the order of Rep_Id, Rep_Cd

View 1 Replies View Related

View 'order By' Not Working

Sep 22, 2006

Hi there,

I have a view created using the following code. The view works perfectly but does not order by the name column as I've asked it to do. In the view designer if I click on execute then the order is applied but if I save the view and run it externally (i.e. in an ASP page or within the management terminal) it does not order correctly and seems to order by the Id column.

Any help would be much appreciated. Here's the code:

SELECT TOP (100) PERCENT dbo.Members.DivisionID, COUNT(*) AS Members, dbo.Country.Name
FROM dbo.Members INNER JOIN
dbo.Country ON dbo.Members.DivisionID = dbo.Country.CountryID AND dbo.Members.CountryID = dbo.Country.CountryID
GROUP BY dbo.Members.DivisionID, dbo.Country.Name
ORDER BY dbo.Country.Name

And another, more simpler view, that doesn't sort as it's supposed to.

SELECT TOP (100) PERCENT CountryID, Name, RegionID, IsActive, HasFlag, URL, Comments
FROM dbo.Country
ORDER BY Name

Many thanks,

Ady

View 5 Replies View Related

Order By In A View In Sqlserver2005

Feb 11, 2006

Hi,

I've a problem with a created view in sqlserver2000 that I'm now using in sqlserver2005.

This is the view :

CREATE VIEW hsarti01_VD1 AS
SELECT TOP 100 PERCENT *
FROM hsarti01 WITH(index(hsarti01_PK))
ORDER BY 1,2 desc,3,4


When I do the "select * from hsarti01_VD1" in sql server 2000, I see in the result that the order by is been using. but in sql server 2005 it's just using the order of the primary key and not the order by !



Has anyone have a solution for it ?

Thanks

View 16 Replies View Related

View, ORDER BY Question

May 27, 2008



I built a view that runs against the aspnet_WebEvent_Events table, part of the ASP.Net Health Monitoring.

Here is the view SQL:
SELECT TOP (100) PERCENT EventTime, EventType, EventSequence, EventOccurrence, EventCode, EventDetailCode, Message, ApplicationPath,
ApplicationVirtualPath, MachineName, RequestUrl, ExceptionType, Details
FROM dbo.aspnet_WebEvent_Events
ORDER BY EventTime DESC

What I found is that when the query is ran within the View Designer that the view is ordered by the correct EventTime (datetime) field.

But when the view is ran from right-clicking on the view and selecting "Open View" the order by does not take place.

Why is this happening and how do I fix it?

View 1 Replies View Related

SQL 2012 :: View Resolution Order

Jun 11, 2015

I'm just wondering what is the resolution or execution order for a query like this (simplified version):

CREATE VIEW ClosedSales AS
SELECT *
from NatSales
where CurrentFlag = 1
and ValidTo <> '9999-99-99'
Select * from ClosedSales
where CustomerId = 10

The thing I want to know is:

- If first the complete dataset from the view is calculated and the the CustomerId=10 filter is applied
- Or the CustomerId=10 condition is added to the where clause in the view definition

My real case is much more complex but that is an interesting point in my performance analysis.

View 2 Replies View Related

View Ignoring Order By - Used To Work In Sql 2000

Dec 13, 2005

This works fine in  SQL 2000, but not in SQL 2005

View 12 Replies View Related

'Open View' Option Does Not Sort Records According To ORDER BY

May 26, 2008

Hi,
I have few views in SQL Server 2005. In Design View, the results of View are ok. In OPEN VIEW option, records are not sorted correctly, ORDER BY is ignored.
What could be the reason for this ?
Thanks a lot in advance! 
 
 

View 6 Replies View Related

Help With Solution For Use Order BY Inside Function And VIEW On The Sql Server

Apr 8, 2008

help with solution for use order BY inside function and VIEW on the sql server
i explain the problem
i am a web developer in asp page
i BUY an DATA grid object for my web site
the problem in this object i can use only ONE field to use ORDER BY
can i store in the sql server function and VIEW that change the ORDER of the result inside the sql server ?
so whan i do
SELECT *
FROM dbo.tb_pivot_big

the sql server ORDER for ME the result and run this (inside the sql server )


Code Snippet
SELECT *
FROM dbo.tb_pivot_big
ORDER BY new_unit, Fname ASC, val_orginal desc



TNX for help

View 14 Replies View Related

SQL 2005 Bug? Not Follow The Order By Sequence In View...but SQL 2000 Does!

Aug 24, 2006

I have a table:

CREATE TABLE [dbo].[tx1]( [f1] [nvarchar](50) , [seq] [int] IDENTITY(1000,1) NOT NULL ) ON [PRIMARY]

SELECT *
FROM dbo.tx1
ORDER BY seq DESC

go

f1 seq
zz 1003
uu 1002
kk 1001
yy 1000


create view vx1 as
SELECT top 100 percent *
FROM dbo.tx1
ORDER BY seq DESC
go
select * from vx1

yy 1000
kk 1001
uu 1002
zz 1003







View 7 Replies View Related

Create A View That Will Give Most Current Status (by Statusdatetime) Of Each Order Number

Jan 20, 2015

We are having trouble figuring out how to create a view for this scenario:

We have a status log table that holds an order number, statusdatetime, and statuscode. This table will have multiple status' for the same order number. I want to create a view that will give me the most current status (by statusdatetime) of each order number. This view would show: order number, statusdatetime, and statuscode.

Here is a sample of the data:

Order numberStatusDateTimeStatusCode
1234512/15/2014 15:00CREATE
1234512/15/2014 16:30CONFIRMED
4567812/16/2014 08:00CREATE
9876412/18/2014 12:00CREATE
9876412/19/2014 08:00CONFIRMED
4567812/17/2014 09:30CONFIRMED
4567812/19/2014 15:30IN-TRANSIT

So my view should result in :

Order numberStatusDateTimeStatusCode
1234512/15/2014 16:30CONFIRMED
9876412/19/2014 08:00CONFIRMED
4567812/19/2014 15:30IN-TRANSIT

View 4 Replies View Related

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz

Jan 7, 2007

Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...

SQL Server Express edition

Visual Studios 2005

Jet 4.0 newest upgrade

.Net Framework 2.0 (or should I use 3.0)

VS2005 Security upgrade

Anything else I need for just creating a database for my VS2005 Visual Basic project?

I was trying to use MS Access as my backend db but would like to try SQL Express



Thank you, Mark





View 7 Replies View Related

Conditional Order By - Sort Result Set By Employee Number Ascending Order

Sep 24, 2012

In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception

1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)

2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)

How shall I achieve this?

View 4 Replies View Related

Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?

May 19, 2015

I never paid much attention to this before but I noticed this today in a new table I was creating.

For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.

I have a date table I setup and I noticed it is NOT respecting the sort order.

I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.

I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.

Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?

View 8 Replies View Related

Calculate Total Amount Of Order Details Based On Particular Order

Apr 10, 2014

I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

View 7 Replies View Related

Default Sort Order - Open Table - Select Without Order By

Mar 27, 2008

Hi!

I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.

The table returns the data in the same order in SQL Manager "Open Table"

So I started to wonder what deterimins the sort order when there is no order by clause ?

I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.

Peace.

/P

View 5 Replies View Related

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?

Jan 4, 2008

Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.

below is a Stored Procedure that i have wrote in creating a PO:



CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)

AS

BEGIN

INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)

END



SET @POno = @@IDENTITY

RETURN


However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?


CREATE TRIGGER trgInsertPOItem

ON PurchaseOrderItem

FOR INSERT

AS

BEGIN


'What do i entered???'
END

RETURN


help is needed asap! thanks!

View 14 Replies View Related

Find Order By Date Range Or Order Id

May 8, 2007

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
 but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez

View 4 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Recordset's Order And Database's Physical Order?

Jul 20, 2005

Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!

View 2 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

Inconsistent Sort Order Using ORDER BY Clause

Mar 19, 2007

I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
----------
Non-A
Non-O
Noni
Nons

Resultset2
----------
Non-A
Noni
Non-O
Nons


Any ideas?

View 4 Replies View Related

SQL To Order Results In Predefined Order

Mar 27, 2008

I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?

View 4 Replies View Related

Force The ORDER BY To Be In Ascending Order??

Jul 5, 2007

I noticed the StockDate is not sorted in proper order, like ascending order...


Code:

select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc



I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd...

The sample data is MM/DD/YYYY...

So, how do I get it ordered propery by Year, Month then Day??

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved