Views Ignoring Order By Clause
Sep 7, 2007
I have just transferred my site to a new server with SBS R2 Premium, so the site's database changed from SQL 2000 to SQL 2005. I find that searches are now returning results in random order, even though they use a view with an Order By clause to force the order I want.
I find that the results are unordered when I test the view with Management Studio, so the issue is unrelated to my VB/ASP Net code.
Using my SQL update tool (SQL Compare, from Redgate) I find that there are no differences in the views, or the underlying tables.
Using Management Studio to test a number of views, I find that I have a general problem with all views. For example, one of the simpler views is simply a selection of fields from one table, with an Order By clause on the tables primary key: -
SELECT TOP (100) PERCENT GDQid, GDQUser, GDQGED, GDQOption, gdqTotalLines, GDQTotalIndi, GDQRestart, GDQCheckpointMessage,
GDQStarted, GDQFinished, gdqCheckpointRecordCountr
FROM dbo.GEDQueue
ORDER BY GDQid DESC
If I right-click the view (from Management Studio's Object Explorer pane), select Design from the menu to show the view's design, and then click the Execute SQL icon, the view's results are displayed perfectly, in descending order of GDQid. However, if I select "Open View" the view's results are displayed out of order.
When I do this with the SQL 2000 database, both Design/Execute and Open View correctly display the data in the correct order.
Is there something that I should check in the SQL 2005 installation - some option that has been set incorrectly?
Regards, Robert Barnes
View 16 Replies
ADVERTISEMENT
Sep 25, 2013
The data I am pulling is correct I just cant figure out how to order by the last 8 numbers that is my NUMBER column. I tried adding FOR XML AUTO to my last line in my query: From AP_DETAIL_REG where AP_BATCH_ID = 1212 and NUMBER is not null order by NUMBER FOR XML AUTO) as Temp(DATA) where DATA is not null
but no change same error.
Output:
1234567890000043321092513 00050020
Select DATA from(
select '12345678'+
left( '0', 10-len(cast ( CONVERT(int,( INV_AMT *100)) as varchar))) +
cast (CONVERT(int,(INV_AMT*100)) as varchar) +
left('0',2-len(CAST (MONTH(DATE) as varchar(2))))+
CAST (MONTH(DATE) as varchar(2)) +
left('0',2-len(CAST (day(CHECK_DATE) as varchar(2)))) +
CAST (day(DATE) as varchar(2))+right(cast
(year(DATE)
[code]....
View 6 Replies
View Related
Sep 24, 2007
Hi,
I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that.
Thanks in advance, John
select part from transactions T where (T.transdate between '20070701' and '20070705') and
(T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part
Here is the beginning of the Transactions table create script
CREATE TABLE [Transactions] (
[RecNo] [int] IDENTITY (1,1) NOT NULL,
[Part] [nvarchar] (30) NOT NULL ,
[TransDate] [nvarchar] (8) NOT NULL ,
[TransType] [nvarchar] (4) NOT NULL ,
[FromLoc] [nvarchar] (10) ,
The 'Part' column is an alphanumeric field. The problem I am having is that the
Order By seems to ignore the hyphen character '-' when the returned rows
are ordered by the Part (which can contain hyphens in any column).
Here is an example of what I get.
130909N9
130909N9
130909N9
1-480698-0 * These two should not be here
1-480699-0 *
15-423
164-07700
164-07700
164-07700
1683
I was expecting this ( and I get this in and older database ).
068-03000
068-03000
06A19956
074-03200
077-367-0
08DU08
1-480698-0 * These should be here eariler in the data
1-480699-0
100-364072
100-364072
View 3 Replies
View Related
Dec 13, 2005
This works fine in SQL 2000, but not in SQL 2005
View 12 Replies
View Related
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
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
Sep 5, 2007
Does anyone know how to count rows of data from 1 table that meet one or two different criteria and then get the probability of occurance for that criteria ... as an output column?
Do I use 'Having' or temp tables or Views?
Here is my output for now. I am trying to solve for 'Prob' - everything else works on it's own.Craig
SELECT Wins_Lng/Trades_Lng as Prob
FROM Transactions
SELECT Count(Ticker) as Trades_Lng
FROM Transactions
WHERE TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND TransKind='B' (SELECT COUNT(Ticker)as Wins_Lng
FROM TransactionsWHERE Transkind='B' AND TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND Profit_Lng>=0)
View 2 Replies
View Related
Jul 19, 2009
I have an Epicor ERP system that enables writing 'queries' in its internal format, that can see Views in the MSSQL database. Very useful, as I can write view definitions with much more flexibility than the ERP tools. As far as I can tell the ERP DB interface can't call SQL procedures.
What I want to to is to pass a value from the ERP engine that the View can use to restrict record selection. The View query produces a field that describes how far down into a nested parts list structure it has got, so the data is not in the database, it is calculated by the query.
A user may wish to restrict the depth of descent to, say 3 levels. I can filter the results set returned by the view in today's solution to only show levels up to 3, but the View still processes all levels (some 12 million records possible return) before the filtering can be applied. SLOW...
So I want to pass a parameter (if that is the right jargon) into the View to limit how much work it will do.
View 2 Replies
View Related
Dec 19, 2005
Is there a way to make EM script all views of a database in the order in which they depend on each other?
View 11 Replies
View Related
Feb 5, 2007
In SQL 2K...i need to write a script that writes a script that creates all views in the order that they are nested and I am feeling lazy. Does anyone have anything handy?For example if View_BOB says...CREATE VIEW VIEW_BOBASSELECT COL_1 FROM VIEW_JOHNI need the script to generate a script that creates View_JOHN before View_BOB.
View 1 Replies
View Related
Jun 4, 2007
I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2
Essentially the problem is as follows:
1) Initial state is that merge replication of table and views is working fine
2) I then alter one view which references a new view in the same publication
3) Synchronization processes the view scripts in the wrong order regardless of the processing order
4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted
The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order
Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs
I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct
Is there an known problem in this area?
Any suggestions would be much appreciated
aero1
View 7 Replies
View Related
May 20, 2014
There are about 300 fields in a views.
How to reorder all fields by alphabet?
For example, from script of views like below:
select name, date, amount from order
re-order fields' name like below:
select amount, date, name from order
For about 300 fields view, how to code to re-order fields' name?
View 6 Replies
View Related
Jun 25, 2015
Using t-sql, how to list all views in dependency order? I'm trying to script out the creation of views, but need to make sure they are in the correct order when i execute them.
View 2 Replies
View Related
Apr 30, 2001
I am looking to get a cyclic order in the order by clause. how do I do this?
for example I have 5 customers with ids like
xyz 1
xyz 2
xyz 3
xyz 4
xyz 5
when I am selecting xyz 3 I want the list to show
xyz 4
xyz 5
xyz 1
xyz 2
xyz 3
How do I do this by using the order by clause?
View 1 Replies
View Related
Sep 5, 2007
Hello,
can any one tell me about the difference between the following queries.
1. SELECT * FROM Symp_User ORDER BY
2. SELECT * FROM Symp_User ORDER BY ASC
I don't think there is any difference in the above queries. kinldy make me clear on this.
thnkx,
rahul jha
View 7 Replies
View Related
Aug 19, 2006
Can you explain the below scenario
The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.
what is the reason behind this.
View 1 Replies
View Related
Jul 20, 2005
If I use the order by clause to sort on a date, where the date andtime stamp are the exact same for multiple records, how does SQLoutput the data?At random... or does it look at the primary key?
View 1 Replies
View Related
Aug 7, 2007
Hello,Ive got a column which stores integers ranging from 0-200. I need to order them so that 1 is first, and 0 is last like 1,2,2,3,4,6,8....98....0,0,0My Order By clause statement looks like 'ORDER BY column_name', but obviously this will put the '0' records at the top. Is there a way around this?Thanks, Curt.
View 4 Replies
View Related
Aug 29, 2007
The following SELECT query gives me a list of 50 plus countries. How do I order them by 'United States' First (happens to be ID 225) and then alphabetical?
SELECT Country_ID, Country_Long FROM Countries WHERE isIndustrial = 1 ORDER BY Country_Long
View 6 Replies
View Related
Mar 19, 2008
Hi! I think the order by clause is driving me crazy.The following T-SQL query works: SELECT
count(*) AS c
From F_POST
Where id=@id
Order by c Ok, so far so good, but in the following case it is NOT possible to order the result set according to "count(*)": Select
T_Date AS TDATE,
count(*) AS c
From F_Post
Where id=@id
Order By case when @OrderBy = 1 then c
elseT_DATE DESC This is just strange since it is essentially the same query!? Furthermore, it seems to be inpossible to have a order-by-clause that looks like: Order By case when @OrderBy = 1 then T_Column1 ASC
elseT_Column2 DESC
Does anyone know how I can implement querys that do (almost) exactly this what the last 2 querys "should" to do?
View 2 Replies
View Related
Dec 1, 2005
like so often my Forums database design (in its simplest form) is:Forums -ForumID -Title -CategoryForumsMsgs -fmID -DateIn -AuthorID -MessageI need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?
View 2 Replies
View Related
Mar 23, 2006
When I say to sort on a datetime field on descending order, the date is sorted. However, the time difference is not reflected in the results.
Any way, we can fix it.
i.e. If I have two records with the same dates but different times, the sorting order is not considering the time.
View 1 Replies
View Related
Jun 26, 2000
I'm using a Case statement to change an Order By clause on the fly, eg
ORDER BY case when @SortBy = 1 then s.ITEM_NAME
when @SortBy = 2 then s.ITEM_ID
when @SortBy = 3 then s.ITEM_SIZE
end
The numeric columns work fine but when @SortBy = 1, I get the following message when I try to run the sp:
Server: Msg 8114, Level 16, State 5, Procedure usp_CML_SAO_RptPresLvl, Line 95
Error converting data type varchar to numeric.
ITEM_NAME is a varchar(40) containing alphanumeric characters; ITEM_ID is a numeric(8,0) & ITEM_SIZE is a varchar(5) containing numeric characters.
Is there some rule preventing me to dynamically change the Order By if using a alphanumeric characters?
Thanks for any help you can offer
Jo
View 2 Replies
View Related
Mar 10, 1999
I am trying to pass as an input parameter a user selected order by clause, and instead of repeating the SQL statement with a new Order By based on the parameter, I want to set the Order by using this parameter. I can't get it to work.
Here is the statement:
Create Procedure sp_InfoDump
(
@StartDate varchar(12),
@EndDate varchar(12),
@OrderBy varchar(50)
)
As
/* Local variables */
DECLARE @MinDate datetime, @MaxDate datetime
IF @StartDate = 'ALL DATES'
BEGIN
SELECT @MinDate = Min(AccessTime)
FROM tblAudit
END
ELSE
BEGIN
SELECT @MinDate = @StartDate
END
IF @EndDate = 'ALL DATES'
BEGIN
SELECT @MaxDate = Max(AccessTime)
FROM tblAudit
END
ELSE
BEGIN
SELECT @MaxDate = @StartDate
END
BEGIN
SELECT tblReports.ReportName, tblReports.ReportCode,
tblAudit.BadAttempts, tblAudit.LogonUser, tblAudit.AccessTime,
tblAudit.RemoteHost, tblAudit.RemoteIdent, tblAudit.ExitTime,
tblAudit.BrowserType, tblAudit.Access_ID, TotalTime=DateDiff(Minute,tblAudit.AccessTime,tblA udit.ExitTime)
FROM tblReports
INNER JOIN
tblReportsAccess ON
tblReports.Report_ID = tblReportsAccess.Report_ID
INNER JOIN
tblAudit ON
tblReportsAccess.Audit_ID = tblAudit.Audit_ID
WHERE tblAudit.AccessTime >= @MinDate AND tblAudit.AccessTime <= @MaxDate
ORDER BY (SELECT 'ColumnName'=ColumnName FROM tblOrderBy WHERE ColumnName = @OrderBy)
END
RETURN
View 1 Replies
View Related
Feb 17, 2005
I have a query that returns several ordered rows where one of the fields in the ORDER BY clause is a date field (DueDate) that we use to see the most pressing deadline first. The problem is that the default value in that field (which other code translates to mean no due date) is 1/1/1900. That means that items with no due date show up before today's import deadline. I can see one potential solution where I join my results on the original table where DueDate>1/1/1900 and then back to my results so I can use an ISNULL() on the field to set a value in the future (like 1/1/9999), but that seems like a really nasty wrong round-about way to do it. I think there has to be something better.
View 4 Replies
View Related
May 21, 2014
I have a a grid (Fig-1) where i have LineID and corresponding RankValue. I want to sort out the Grid like (Fig-2) where It will be sorted based on Rank Value(Higher to lower) but LineID group should maintain. I am using SqlServer 2008.
View 3 Replies
View Related
Mar 29, 2004
Need to pass column to ORDER BY as parameter in sp (possible 8 columns out of total 30). Is there a way to do it avoiding dynamic SQL use(will be used frequently)?
View 1 Replies
View Related
Dec 7, 2007
Hi,
I'm currently have a problem with a query using a top clause. When I run it by itself as a single query, I have no problems and the results are valid. However, if I try duplicate the query after a union clause, the order by ... desc doesn't order properly.
The following is the query I'm using along with the results. Then I'll have the query I was trying to unite and the results (date ranges selected were the same in both):
QUERY 1
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=20411
order by s.ldate desc
RESULTS 1
DATE MDT IDPU Odometer DO Odometer Total Miles
12/6/2007 2041112810.6 12874.5 63.9
QUERY 2 (with Union)
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=[From Date,Date]
and s.ldate<=[To Date,Date]
and v.mdtid=20411
Union
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=2642
order by s.ldate desc
RESULTS 2
DATE MDT ID PU OdometerDO Odometer Total Miles
4/10/2007 20411 1207.21252.5 45.3
1/2/2007 2642 193652.6193817 164.4
As you can see, the results are sorted very differently. Is there any way to have the order by apply to both queries?
Thanks!
Craig
View 5 Replies
View Related
Jul 20, 2005
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
View 7 Replies
View Related
May 12, 2008
Hi,
Im using a select query in which im using order by clause on a column which is varchar.
Im getting wrong result on using the query,
the result output is below
1036
1373
1610
2324
255
2819
324
459
477
581
698
831
can anyone help
View 2 Replies
View Related
Dec 12, 2007
For my reports I have a Sort By parameter which has 2 values - Customer Name & Customer Number. for my dataset I have added @SortBy as parameter and assigned the value = Parameter!SortBy.value.
In the query I want to set the Order By clause based on the user selection. eg.:
select * from dbo.customers where name = @CustomerName order by @SortBy
However, I am unable to do this. I always get an exception for the order by clause no mater what. I have also tried the following queries in the query designer for the dataset customers but none of them work
="select * from dbo.customers where name " + @CustomerName + " order by " + @SortBy
select * from dbo.customers where name = @CustomerName order by + @SortBy
I know that I can set the interactive sort on the column headers and the interactive sort works, but the customer wants to have the ability to set the Sort By using the dropdown list.
Any input would be appreciated.
Thanks!
Arpan
View 9 Replies
View Related
Sep 9, 2015
i want to know how to use order by clause in row wise
for example, suppose the rows is
10 20 30 40
so the output is should be
10
20
30
40
View 3 Replies
View Related
Sep 13, 2007
Hi,
I am getting a wierd error while using order by in the over clause. Consider the following query:
select count (*) over (order by STD_CLL_CNTR_KEY) as cnt
from FCT_CLL_CS_DTLS
The error reported is :
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'order'.
But at the same time this particular query seems to be working fine:
select rank () over (order by STD_CLL_CNTR_KEY) as cnt
from FCT_CLL_CS_DTLS
Am I missing something fundamental or is there a bigger issue.
Thanks in advance,
Regards,
Emil
View 3 Replies
View Related
