SQL7: Order Of Values In IN Clause Affects Results

Dec 14, 1999

Hi!
Has anyone experienced this problem?
Certain queries that work fine in SQL 6.5 and Oracle return inconsistent / inaccurate results in SQL 7 (with SP1). These queries include an IN clause with a range of values.
For example, the following query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,2,3,11,19)
go

returns a different result than this query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,3,11,2,19)
go

The only way we have stumbled upon to get accurate results consistently is to order the range values from largest to smallest:
AND columnA IN (19,11,3,2,1)

Have not seen this documented anywhere. We are in the process of re-ordering these ranges in our code, but I welcome any ideas or comments...
Thanks!

View 2 Replies


ADVERTISEMENT

Ordering Results By Order Of The IN' Clause

Jul 28, 2006

Consider this SQL:SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1','value3')Simple enough, but is there anyway to specify that the result should beordered exactly like the "IN" clause states? So when this recordsetcomes back, I want it like this:my_field------------value2value1value3Possible?Deane

View 5 Replies View Related

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

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

Changing SQL7 Sort Order

Nov 7, 2000

For a variety of reasons we need to change the default sort order in a SQL7 installation on about 25 servers. It's currently case insensitive, but we need to change it to case sensitive. To do this we propose to deinstall and reinstall SQL. In order to preserve the data in the tables can anyone suggest the correct procedure.

Thanks

Mark

View 2 Replies View Related

ID ORDER PROBLEM IN TABLES IN SQL7 DATABASE

Oct 25, 2001

I have a problem with the order of the numeric ID in several of my tables in one database.
Basically every quarter, using link table i update the figures in my table using basic cut and paste. However this will not work now as the table has become out of order due to the ID'S not beeing in numeric order.example
ID
1
7
23
24
15
16
2
3
8
34

I am desperate to get these collums back into order so i can paste my data in how do i do this. i want it to be like this

ID
1
2
3
4
5
ECT

PLEASE HELP

THANKS

View 2 Replies View Related

Filtering Results In The Where Clause Vs A Having Clause

Oct 25, 2007

I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.

When on SQL Server 2000 the following statement ran without issue:

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN

(


SELECT TrackID

FROM dbo.Track_ID

GROUP BY TrackID

HAVING MAX(LegNum) = 1 AND


TrackID + 'x1' IN


(


SELECT

dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)

)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.

I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?


UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN
(



SELECT TrackID

FROM dbo.Track_ID

WHERE TrackID + 'x1' IN


(


SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)
GROUP BY TrackID

HAVING MAX(LegNum) = 1

)

View 3 Replies View Related

Transfer View Results In SQL7 To Access/Excel

Apr 29, 1999

Hi,

Does anyone know the method to transfer result data of a view or stored procedure
in MS SQL 7.0 to MS Access or Excel , Manually or automatically? I did not find the
right tool in SQL 7 to do so.

I notice we can transfer table from SQL 7 to MS Access / Excel through DTS. But did not
find any tool/menu to transfer result of view / stored procedure.

I am new to SQL 7. Maybe this is a silly ?. Any help will be appreciated.

Charlie

View 2 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

Specify Order For Select Results, Order By: Help!

Nov 17, 2006

Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.

My query would look something like this:

Select leadershipName
From Leadership

Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:

Executive Board Members, Delegates, Grievance Chairs, and Negotiators

My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?

Thanks,

Zoop

View 1 Replies View Related

Access97 To SQL7 DTS For Time Values

Mar 17, 2000

When Access date/time data is transformed to SQL7, it seems to work fine as long as the data contains date information. But, if the data is time only, there is the 2-day diff. coming out of Access.

For date/time data in Access, day 0 is 12/30/1899--for SQL7 it is 1/1/1900.

So, it appears that DTS first converts Access date/time data to a date formatted string and then sends to SQL. This will only be a problem for times....8:00 AM is stored to SQL as 8:00 AM on 12/30/1899, not 1/1/1900. If I try to calculate hours or minutes from midnight (date/time 0) I get a negative number.

Has anyone else encountered this? I guess my choice is to modify DTS to correct for the dates or to run an update once the data is moved.

View 1 Replies View Related

Order By Clause

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

Order By Clause

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

Reg. Order By Clause

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

Order By Clause

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

Use Order By But Want NULL Values As High Values

Nov 9, 2000

Hi,

My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list

e.g.

Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -

At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?

Thanks
Dan

View 1 Replies View Related

Order By Clause Problem

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

A Special Order By Clause?

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

T-SQL : Order By Clause Is Just Strange

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

JOIN With ORDER BY Clause?

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

Table Order By Clause

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

Changing Order By Clause On The Fly

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

Order By Clause Using A Variable

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

Exception For An ORDER BY Clause

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

Using Group By And Order By Clause

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

Problems With ORDER BY Clause

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

Top Clause , Union And Order By

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

SQL Query Help-- Order By Clause

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

Problem While Using Order By Clause

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

Order By Clause Using Parameters

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

How To Use Order By Clause Row Wise

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

Error In Use Of Order By In Over Clause

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

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 View Related







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