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.
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?
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));
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.
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?
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.
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
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?
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.
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.
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
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.
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.
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)?
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?
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
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
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.
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
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?
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...
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 ?
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
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?
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
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
