Problem While Using Order By Clause

Inconsistent Sort Order Using ORDER BY Clause
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 Replies !

---

Order By Clause In View Doesn't Order.
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 Replies !

---

Order By Clause
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 Replies !

---

Order By Clause
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 Replies !

---

Order By Clause
Hi there,
i'm trying to order the follow query:


SELECT count(nome) as CNT FROM utenti WHERE nome like '%" & rs("nome") & "%' ORDER BY CNT ASC

why i've this error:

Microsoft OLE DB Provider for ODBC Drivers errore "80040e10"
[Microsoft][Driver ODBC Microsoft Access] Parametri insufficienti. Previsto 1.

Thank you in advance...

View Replies !

---

Order By Clause
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 Replies !

---

Order By In() Clause ??
Hi!

My problem is this one:

I'm doing a simple query like this (simplified):

SELECT Me_id, Det_id, Det_val
FROM oFormsMsgDet
WHERE (Det_id IN (2411, 2409, 2410, 2408))

And I want the recordset order by my IN list. My In list is given dynamicly so I can't play with it and my query is returned in Det_id order. How can I do it?

Thanks a lot

Genviou

View Replies !

---

Reg. Order By Clause
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 Replies !

---

Order By Clause Problem
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 Replies !

---

T-SQL : Order By Clause Is Just Strange
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 Replies !

---

A Special Order By Clause?
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 Replies !

---

Table Order By Clause
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 Replies !

---

JOIN With ORDER BY Clause?
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 Replies !

---

SQL Query Help-- Order By Clause
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 Replies !

---

Problems With ORDER BY Clause
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 Replies !

---

Order By Clause Using A Variable
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 Replies !

---

Changing Order By Clause On The Fly
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 Replies !

---

Exception For An ORDER BY Clause
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 Replies !

---

Order By Clause Using Parameters
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 Replies !

---

Error In Use Of Order By In Over Clause
 

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 Replies !

---

Top Clause , Union And Order By
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 Replies !

---

Remove Certain Words From ORDER BY Clause
Hi,I'm trying to remove certain words from my Order clause. For example on iTunes they have removed the word 'The' from the start of artist names so that all the bands that start with 'The' don't appear grouped together. I'm trying to do a similar thing with University names so that all universities which begin with 'University of' or 'The' don't appear together.Is this possible?Thanks for your help!,Curt.

View Replies !

---

Need An Special ORDER BY Clause Query
Table:ColumnsUsersList:UserID, UserName, Country
I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order
So the query first return all the users from Pakistan and the the users from rest of the world in ascending order.
Forexample,
1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan
then the query returns.
3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA

View Replies !

---

Views Ignoring Order By Clause
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 Replies !

---

ORDER BY Clause Does Not Work In SQL 2005?
Hi,
A quick background on the problem;
My company is in the process of a migration from Windows Advanced Server 2K, SQL 2K to Server 2003 and SQL 2005. I'm not certain of the exact process used by our DBA to convert the DB, but I can access it, and all my tables/views/sprocs appear to be in the right place.
I copied all my web files to our new server after the DBA was done with her job, made a new user on the new instance of SQL server, changed a few connection strings in my global.asa and global.asax, and ta-dah! Just like magic, the new site opened on our new servers without much resistance.
Except....
None of the content on our sites is sorted. I cannot seem to get ORDER BY statements to work at all. They appear to be disregarded by SQL server when not in MODIFY mode for a particular view (in SQL Server Management Studio).
So, when I MODIFY a view, add criteria (NOT SORT), save the changes, then OPEN, the criteria is respected. The filter is applied. BUT...
If I MODIFY a view and add a SORT using ORDER BY (by hand or with the Manager) the sort is NEVER respected when the view is Opened through the manager or in my code.
If I open any sorted view and then click MODIFY, and then RUN (without making ANY changes), the sort works with no problem whatsoever.
To summarize/restate my case, if I OPEN any view in the system that has an ORDER BY criteria, the sort is NOT APPLIED. If I instead right-click and MODIFY, then click RUN, the SORT is APPLIED.
I've tried sorting datetime and text fields, all with the same results - none. This single dumb issue has been delaying the migration of our servers for days!
Can anyone help?
 
Thanks always in advance,
Drew
 

View Replies !

---

ORDER BY Clause With Multiple Tables
Hi AllI am having a problem with an ORDER BY clause when selecting information from multiple tables. EgSELECT i.InvoiceId, pd.PayDescription, u.UserNameFROM   Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId                       LEFT OUTER JOIN tblUsers ON i.UserId = u.UserIdORDER BY pd.PayDescriptionthis is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?I am writing this for MSSQL Server 2000ThanksBraiden

View Replies !

---

Query Performance With Order By Clause?
Hi all,Just wondering if anyone can tell me if an order by clause on a selectquery would have any impact on the time it takes to retrieve results?Essentially I'm selecting Top 1 out of a table via various criteriaand currently getting it back without an order by clause. The order bywould only include the column that has the clustered primary index onit.Can anyone tell me if in theory this will slow the query down?Many thanks in advance!Much warmth,Murrau

View Replies !

---

Question On SQL Server2000's Order By Clause
Hi,
I have problem of order by clase.
Must I specify the column in both select clause and order by clause
so as to get the correct result?

If I omit the order by column in select clause(for example:
select order.*, cl.ID from T_ORDER order, T_CLIENT cl where .... order by cl.code),
would MS SQL server 2000 still correctly or ignore order by clause completely?


It seems the latter actually happens.

BTW, how would other DBMS handle this case?

Regards,
Justin

View Replies !

---

'Order By' Clause Work Incorrect
when i try the following SQL batch, I get a result-set which is not order by
datetime column 'out_date',but if I delete clause INTO #fifo_temp, I get a correct result with correct order.

who can help me?thanks in advance
...
select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

DROP TABLE ##stuff_fifo
select * from #fifo_temp

the following can get a correct result:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

View Replies !

---

ORDER BY Clause In A Stored Proc?
Can you put an ORDER BY clause in a stored procedure? What I'd like to do is have a stored procedure where the proc could be called, with an ORDER BY clause passed on as a variable,

as in:
CREATE PROCEDURE dbo.select_all_from_users
@order_by varchar(100)
AS

SELECT * from USERS
ORDER BY @order_by;

This doesn't work, I get the following nastygram thrown in my face "Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression itentifying a column position. Variables are only allowed when ordering by an expression refrencing a column name."

That's where I'm stuck. The variable @order_by WILL be refrencing a column name, at least it will in my opinion, but the SQL Server doesn't think so...

Any ideas or workarounds?

Alan McCollough

View Replies !

---

Row Size Confict In Order By Clause
Hi,
In SQL server 7.0 length of datatype char can be 8000. Suppose i have defined a field of type char with length 8000. Now if some result set containing the field with length 8000 and some other fields so that length of rowsize returned is more than max rowsize(i.e 8060)is returned through a stored procedure using ORDER BY it gives error message. Error message says that it can not select a row because rowsize exceeds the max limit in temp DB.
While ordering the resultset tempDB is used for some temporary operations which causes creation of a temporary table in tempDB.
Is there any option or wayout to get a result set(using order by clause)
which crosses the limit of max rowsize. If a normal(Withou ORDER BY) resultset can be obtained than why not with ORDER BY ?

Thanks in advance

Regards
Rahul

View Replies !

---

Migartion Issue With ORDER BY Clause
Please help me how to write the following query in SQL Server 2005

SELECT 3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
        CONVERT(char(10), fulldate, 126),
        CONVERT(char(8), fulldate, 108),
        flag = CASE Invalid WHEN 'Y' THEN 2 ELSE 1 END,
        srk
 FROM #raw
 ORDER BY tag, parent, 'Value!3!date' asc

View Replies !

---

Sql Error-order By Clause Not Working
Hi,
 
 
I have the following code and output
 

select distinct week ,sum(itemvalue) as itemvalue from (Select ATP,

WEEK=

CASE

WHEN (datepart(Dd, ATP) < 7 AND datename(Month,ATP)='JANUARY')

OR

( datepart(Dd, ATP) < 8 AND datename(Month,ATP)='JULY')

OR

( datepart(Dd, ATP) < 7 AND datename(Month,ATP)='OCTOBER')

THEN '1'

WHEN (datepart(Dd, ATP) < 14 AND datename(Month,ATP)='JANUARY')

OR

( datepart(Dd, ATP) < 15 AND datename(Month,ATP)='JULY')

OR

( datepart(Dd, ATP) < 14 AND datename(Month,ATP)='OCTOBER')

THEN '2'

WHEN (datepart(Dd, ATP) < 21 AND datename(Month,ATP)='JANUARY')

OR

( datepart(Dd, ATP) < 22 AND datename(Month,ATP)='JULY')

OR

( datepart(Dd, ATP) < 21 AND datename(Month,ATP)='OCTOBER')

THEN '3'

WHEN (datepart(Dd, ATP) < 28 AND datename(Month,ATP)='JANUARY')

OR

( datepart(Dd, ATP) < 29 AND datename(Month,ATP)='JULY')

OR

( datepart(Dd, ATP) < 28 AND datename(Month,ATP)='OCTOBER')

THEN '4'

WHEN ((datepart(Dd, ATP) IN (29,30,31)) AND datename(Month,ATP)='JANUARY')

OR

( (datepart(Dd, ATP) IN (29,30,31)) AND datename(Month,ATP)='JULY')

OR

( (datepart(Dd, ATP) IN (28,29,30,31)) AND datename(Month,ATP)='OCTOBER')

THEN '5'

WHEN (datepart(Dd, ATP) < 4 AND datename(Month,ATP)='FEBRUARY')

( datepart(Dd, ATP) < 5 AND datename(Month,ATP)='AUGUST')

OR

( datepart(Dd, ATP) < 4 AND datename(Month,ATP)='NOVEMBER')

THEN '5'

WHEN (datepart(Dd, ATP) <11 AND datename(Month,ATP)='FEBRUARY')

OR

( datepart(Dd, ATP) < 13 AND datename(Month,ATP)='MAY')

OR

( datepart(Dd, ATP) < 12 AND datename(Month,ATP)='AUGUST')

OR

( datepart(Dd, ATP) < 11 AND datename(Month,ATP)='NOVEMBER')

THEN '6'

WHEN (datepart(Dd, ATP) < 18 AND datename(Month,ATP)='FEBRUARY')

OR

( datepart(Dd, ATP) < 19 AND datename(Month,ATP)='AUGUST')

OR

( datepart(Dd, ATP) < 18 AND datename(Month,ATP)='NOVEMBER')

THEN '7'

WHEN (datepart(Dd, ATP) < 25 AND datename(Month,ATP)='FEBRUARY')

OR

( datepart(Dd, ATP) < 27 AND datename(Month,ATP)='MAY')

OR

( datepart(Dd, ATP) < 31 AND datename(Month,ATP)='AUGUST')

OR

( datepart(Dd, ATP) < 25 AND datename(Month,ATP)='NOVEMBER')

THEN '8'

WHEN ((datepart(Dd, ATP) IN (25,26,27,28)) AND datename(Month,ATP)='FEBRUARY')

OR

( (datepart(Dd, ATP) IN (27,28,29,30,31)) AND datename(Month,ATP)='MAY')

OR

( (datepart(Dd, ATP) IN (25,26,27,28,29,30)) AND datename(Month,ATP)='NOVEMBER')

OR

( (datepart(Dd, ATP) <2) AND datename(Month,ATP)='DECEMBER')

THEN '9'

WHEN (datepart(Dd, ATP) < 11 AND datename(Month,ATP)='MARCH')

OR

( datepart(Dd, ATP) < 9 AND datename(Month,ATP)='SEPTEMBER')

OR

( datepart(Dd, ATP) < 9 AND datename(Month,ATP)='DECEMBER')

THEN '10'

WHEN (datepart(Dd, ATP) < 18 AND datename(Month,ATP)='MARCH')

OR

( datepart(Dd, ATP) < 16 AND datename(Month,ATP)='SEPTEMBER')

OR

( datepart(Dd, ATP) < 16 AND datename(Month,ATP)='DECEMBER')

THEN '11'

WHEN (datepart(Dd, ATP) < 25 AND datename(Month,ATP)='MARCH')

OR

( datepart(Dd, ATP) < 23 AND datename(Month,ATP)='SEPTEMBER')

OR

( datepart(Dd, ATP) < 23 AND datename(Month,ATP)='DECEMBER')

THEN '12'

WHEN (datepart(Dd, ATP) > 24 AND datename(Month,ATP)='MARCH')

OR

( datepart(Dd, ATP) > 22 AND datename(Month,ATP)='SEPTEMBER')

OR

( datepart(Dd, ATP) < 30 AND datename(Month,ATP)='DECEMBER')

THEN '13'

ELSE 'BEYOND'

END , SUM(ITEMVALUE) as ITEMVALUE

 

FROM tOPENLINE_MODIFIED

LEFT OUTER JOIN

tZCHANNEL ON tOPENLINE_MODIFIED.ZCHANNEL = tZCHANNEL.ZCHANNEL

WHERE RequestQtr in ('Q4')

and tOPENLINE_MODIFIED.ATP >= '7/01/07'

and tOPENLINE_MODIFIED.ORDERTYPE in ('OR','ZBOS','ZECM','ZOR','ZOB','ZEXP')

and dbo.tZCHANNEL.ZCHANNEL in ('D','I','01', '02', '06', '07', '10')

and tOPENLINE_MODIFIED.ACCTASSIGNGRP in ('01','02')

AND tOPENLINE_MODIFIED.SOLD2NAME NOT LIKE ('%celestica%')

AND tOPENLINE_MODIFIED.SOLD2NAME NOT LIKE ('%giant%')

and tOPENLINE_MODIFIED.PLANT IN ('COF1', 'I405', 'I375', 'IOM4', 'IOM5', 'I316')

GROUP BY ATP)as A

GROUP BY week
 
output:

week     itemvalue

------      ---------------------

1         1214003.60

10       9257193.45

11       12095432.11

12       11429629.08

13       7315751.08

2         1052337.53

3         951038.10

4         274769.21

5        465278.37

6        78003.67

7        607681.02

8        9042948.17

9        2255545.25
 
but i need the output as
 
week
1
2
3
4
5
6
7
8
9
10
11
12
13
 
iam not able to achieve this after trying so many times.Please help me on this.
 
Thanks,
SVGP
 

 

 

 

 

 

 

 

 

View Replies !

---

How To Get Resultset In The Order Of Items Given In IN Clause
 

My query is

select * from Items where ItemId in
(4354,14759 ,62990,105170,105244,14741,58495,14742,49798,14743,58496,14744,71631)

I'm getting the resultant set in the random order.. like this


4354
14741
14742
14743
14744
14759
49798
58495
58496
62990
71631
105170
105244


How to retrive as same as i have given inside IN clause

i.e --> In this order 4354,14759 ,62990,105170,105244,14741,58495,14742,49798,14743,58496,14744,71631

Any help would be very uself.

Thanks in Advance

Vidhya

View Replies !

---

How To Pass Dynamic Parameter To Order BY Clause
Hi,

I use a DataGrid to show the data, and I want it has a sorting and Paging function,
so I use dataset to collect the data from middle tier function and stored procedure.
I have code in aspx page like BindData(ViewState("SortExpr")).

In the stored procedure I pass SortExpr as parameter as following:

CREATE Procedure Ruying_AutoSearch10
(
@Make varchar(50),
@Model varchar(50) = NULL,
@Condition varchar(20) = NULL,
@Miles float,
@Zipcode varchar(5),
@SortExpr varchar(100)
)
AS

DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'

if @RowCount > 0
BEGIN
SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear, a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @Make
AND a.Model = IsNull(@Model,a.Model)
AND a.Condition = IsNull(@Condition, a.Condition)
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles
ORDER BY @SortExpr
END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...
GO


but I got the error as "variables are only allowed when ordering by an expression referenceing
a column name". How I fix this error? Please help.

Thanks.

Lin

View Replies !

---

Parameterized Order By Clause: Doesn't Work
Can someone tell me why SQL seems to ignore my order by clause?I tried to run through the debugger, but the debugger stops at theselect statement line and then returns the result set; so, I have noidea how it is evaluating the order by clause.THANK YOU!CREATE proc sprAllBooks@SortAscend varchar(4),@SortColumn varchar(10)asIf @SortAscend = 'DESC'Select titles.title_id, title, au_lname, au_fname,Convert(varchar(12), pubdate, 101) as PubDatefrom authorsinner jointitleauthoronauthors.au_id = titleauthor.au_idinner jointitlesontitleauthor.title_id = Titles.title_idORDER BY au_lnameCASE @SortColumn WHEN 'title' THEN title END,CASE @SortColumn WHEN 'au_lname' THEN au_lname END,CASE @SortColumn WHEN 'PubDate' THEN PubDate ENDDESCELSESelect titles.title_id, title, au_lname, au_fname,Convert(varchar(12), pubdate, 101) as PubDatefrom authorsinner jointitleauthoronauthors.au_id = titleauthor.au_idinner jointitlesontitleauthor.title_id = Titles.title_idORDER BYCASE @SortColumn WHEN 'title' THEN title END,CASE @SortColumn WHEN 'au_lname' THEN au_lname END,CASE @SortColumn WHEN 'PubDate' THEN PubDate ENDGO

View Replies !

---

Ordering Results By Order Of The &"IN' Clause
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 Replies !

---

Order By Clause With Variables In A Stored Procedure
Hi,
I need to include two input variables
in my Order By Clause in a stored procedure like ORDER BY @column @Dirction. But MS SQL does not allow me
to do so and gives an Error 1008.
How can i solve this problem?

Thanks for your help!!

View Replies !

---

SQL7: Order Of Values In IN Clause Affects Results
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 Replies !

---

ERROR: The Nested Query May Be Missing An ORDER BY Clause.
Hi,

on executing the below query i am getting the following error

ERROR:  Errors in the back-end database access module. Nested table keys in a SHAPE query must be sorted in the same order as the parent table. The nested query may be missing an ORDER BY clause.

even though the order by clause is presenet in the nested query

SELECT  t.[ProductId],  Predict ([Association].[Product Basket],3)
From
  [Association]
PREDICTION JOIN
  SHAPE {
  OPENQUERY([Adventure Works Cycle MSCRM],
    'SELECT DISTINCT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')}
  APPEND
  ({OPENQUERY([Adventure Works Cycle MSCRM],
    'SELECT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')}
    RELATE [ProductId] To [ProductId]
  )
    AS
      [Product] AS t
ON
  [Association].[Product Id] = t.[ProductId] AND
  [Association].[Product Basket].[Product Id] = t.[Product].[ProductId]

View Replies !

---

How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?
Hi,
I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow...
Can anyone help? Thanks in advance
Gemma

View Replies !

---

Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement
Greetings,
 
I have a C# application that calls a stored procedure to query the database (MSSQL 2005).   I only have one field/column returned from the query but I need that column ordered.
 
How do I use the ORDER BY clause without returning the index column which does the sorting?  The first example is NOT what I want.  I want something that works like the second example which only returns the 'Name' column.
 

ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name, A.index

FROM
...
...
ORDER BY A.[Index], A.Name ASC

END
 
 

ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]

END
 
Thanks

View Replies !

---

INSERT INTO OPENROWSET Does Not Respect ORDER BY Clause On SQL Server 2005 EE
Hi,
I need to pass data from a SQL Server data base to an Access data base. To do this I use the OPENROWSET as followed:
FR


INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:Aux.mdb'; 'Admin'; '',Test) (Id, Name, TypeId) SELECT Id,Name,TypeId
FROM Test
ORDER BY TypeId


FR

On SQL Server 2000 or MSDE the data is transfered as expected, respecting the specified order. But when I run the same clause on a SQL 2005 EE the data is transfered, but the order is not respected.
So my question is if I have to activate an option for the order to be respected or if this is a bug.

Best regards,
Ângelo Vilela

View Replies !

---

Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile...


Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.

However the select statement itself runs perfectly well as a query, no errors.

The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.

What gives with this?

Thanks in advance
R.

The code:




Code Snippet

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
    DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author:        -----
-- Create date:
-- Description:    inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
    -- Add the parameters for the stored procedure here
    @importedReportID int,
    @authCode varchar(12)
AS
BEGIN
    DECLARE @errmsg VARCHAR(80);

--    SET NOCOUNT ON added to prevent extra result sets from
--     interfering with SELECT statements.
    SET NOCOUNT ON;

    --IF (@authCode <> 'TX-TEC')
    --BEGIN
     --   SET @errmsg = 'Unsupported reporting format:' + @authCode
      --  RAISERROR(@errmsg, 11, 1);
    --END

    DECLARE srcRecsCursor CURSOR LOCAL
    FOR    (SELECT
           ImportedRecordID
          ,ImportedReportID
          ,AuthorityCode
          ,[ID]
          ,[Field1] AS RecordType
          ,[Field2] AS FormType
          ,[Field3] AS ItemID
          ,[Field4] AS EntityCode
          ,[Field5] AS LastName
          ,[Field6] AS FirstMiddleNames
          ,[Field7] AS Title
          ,[Field8] AS Suffix
          ,[Field9] AS AddressLine1
          ,[Field10] AS AddressLine2
          ,[Field11] AS City
          ,[Field12] AS [State]
          ,[Field13] AS ZipFull
          ,[Field14] AS OutOfStatePAC
          ,[Field15] AS FecID
          ,[Field16] AS Date
          ,[Field17] AS Amount
          ,[Field18] AS [Description]
          ,[Field19] AS Employer
          ,[Field20] AS Occupation
          ,[Field21] AS AttorneyJob
          ,[Field22] AS SpouseEmployer
          ,[Field23] As ChildParentEmployer1
          ,[Field24] AS ChildParentEmployer2
          ,[Field25] AS InKindTravel
          ,[Field26] AS TravellerLastName
          ,[Field27] AS TravellerFirstMiddleNames
          ,[Field28] AS TravellerTitle
          ,[Field29] AS TravellerSuffix
          ,[Field30] AS TravelMode
          ,[Field31] As DptCity
          ,[Field32] AS DptDate
          ,[Field33] AS ArvCity
          ,[Field34] AS ArvDate
          ,[Field35] AS TravelPurpose
          ,[Field36] AS TravelRecordBackReference
      FROM ImportedNativeRecords
      WHERE ImportedReportID IS NOT NULL
      AND ReportType IN ('RCPT','PLDG')
     ORDER BY ImportedRecordID  -- this should work but gives syntax error!
    );

END

View Replies !

---

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz
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 Replies !

---

Default Sort Order - Open Table - Select Without Order By
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 Replies !

---

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?
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 Replies !

---

Find Order By Date Range Or Order Id
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 Replies !

---