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?
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,
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:
In MSSql,"Order by" and "distinct" can be used in view?
thanks!
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 RelatedI 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
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 RelatedI 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 RelatedWhen I create a view in SQL and include an ORDER BY clause i can see itin Management Studio. However, when I call the same view from an ASPpage the order goes completely haywire.Any ideas?
View 24 Replies View Related
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
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
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
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
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?
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.
This works fine in SQL 2000, but not in SQL 2005
View 12 Replies View RelatedHi,
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!
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
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
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
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
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?
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?
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?
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
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!
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
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?
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 RelatedHi,
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?
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?
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 RelatedI 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??