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
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 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?
Hello all, has anybody migrated the BCM database from a stand alone PC to Small Biz Server? We fail to import the data in a correct way. After importing the data into BCM on SBS the clients do not recognise the BCM database any more. Thank you!
View 3 Replies View RelatedI 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?
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
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.
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 RelatedHello,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 RelatedThe 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
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?
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 RelatedWhen 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.
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
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
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 RelatedI 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 RelatedNeed 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 RelatedHi,
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
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 RelatedHi,
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
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
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
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
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
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
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 8 Replies View RelatedHi 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 6 Replies View RelatedHi,
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
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
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
I could write a query with a sub-query in order to perform an UPDATE on the most recent 60,000 records of a table based on a date field, but unfortunately I am receiving an error.
Code:
SELECT * FROM DMTM
SET transmit_date = '2012-05-07 00:00:00.000', transmit_status = '1223'
WHERE temp_pk in
[code]...
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