Ok, it is a given that sorting operations are sometimes expensive in SQL Server using the ORDER BY clause because the relational model is not really well suited to sort data blah blah blah.
So the canned DBA response is "this is a presentation issue, handle it in the presentation layer".
OK, great. Problem solved. So I am digging around in the ADO.Net object model trying to give a junior developer some guidance and i am trying to figure out what is the best way in ADO.Net to order what we used to call a recordset where the sorting is not done at the SQL Server, but in IIS.
I imagine there is some overhead in this operation as well. Is it really going to save me some overall performance or am I just shifting the burden and I will not likely see any appreciable difference?
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.
ok..here goes..I have a name field in my table called "bname"all of the data in this field has full names in it, spaces areseparated by a "+"ie. "john+doe"i have two other fields that are currently NULL. "First_name" and"Last_name"now my question is obvious.I need to populate "First_name" with all of the data in "bname" to theleft of the "+"and likewise to the Right of the "+" in "Last_name"i sort of know how i would do this in ASP... but im trying to makethis a DTS package... so i was wondering if this could be done in SQLThanks for any help! :DMario C.
Hello all, The problem goes like this. I am trying to upsize all my data to a SQL server and get it out of the old Access 97 database. One of our tables involves testing reports which tell if an object has passed or failed and its main key is it's ID autonum. When I look at the table in Access, I see that the first record in the table has a date of 1/01/97 with an id of 1 and continues through today. When I bring those records into SQL 7, the first record contains a date of 9/01/99 and continues out of order down the table. I need the data in the SQL server to begin with the 1997 record like it is in Access and not with the 1999 record. I cannot have the records appearing in the table as 20, 3, 4, 5, 1, 2 but I need it as 1, 2, 3, 4, 5, 6, because the id must be in sync correctly. How can I sort the data in SQL 7 ?
I got one prob and i need help to solve it. I got this one field in a table which contain a lot of datas and i need to sort it out into as many different data as a field for each. Example:
I've been tasked with designing a database to store collectiveagreements. I need to store each sub-article of each article of eachagreement in it's own row within a table. I'm having difficultyfiguring out the best way to store each sub-article's identifier sothey sort properly.The majority of the collective agreements use numbers in a dottednotation, for example:1.11.21.3.11.3.21.3.31.4Some agreements mix letters with numbers, such as:A-1.11.1.A.1Any ideas how I should deal with this?
can anyone please help on a sql statement that would sort out three kinds of data. let's say i have a column that contains a status flag. there are only 2 possible values, add and delete. now, i want to sort out three kinds of data: add_only, delete_only, and update. Update is the data that contains duplicate records in the table. Which means, there are two rows having same "student_number" but two different status flags. This happens when there's an update or change in data, such as new address or new phone number. The old row/data is sent with status flag of delete, while the updated data is sent with status flag of add. Example below. An output table with a new column of tag is created.
reports table
STUDENT_NO NAME ADDRESS PHONE STATUS FLAG
1 McKenzie Ohio 9111111 add
2 Hario New Jersey 1234567 add
3 Oda Japan 9876543 delete
4 Davis Chicago 1112222 add
5 Rodriguez Detroit 4445555 add
1 McKenzie Ohio 7778888 delete
4 Davis Detroit 1112222 delete
create, remove, update table
STUDENT_NO NAME ADDRESS PHONE STATUS FLAG TAG
2 Hario New Jersey 1234567 add create
5 Rodriguez Detroit 4445555 add create
3 Oda Japan 9876543 delete remove
1 McKenzie Ohio 9111111 add update
4 Davis Chicago 1112222 add update
Anyone knows what SQL statements to use to have this output? Thanks.
I need help in sorting data return from database. For example, I have the following data:ID field1 1 Computer 2 Cell Phone 3 iPod 4 Car I know that a select statement will look something like this: SELECT field1 FROM table ORDER BY field1. However, I want the data to be display this way in the gridview: ID field1
3 iPod
1 Computer
2 Cell Phone
4 Car Any idea of how I can make this happen. Any idea is appreciated.
I want to select the below info from a sql 2005 db, to use it in a dynamic datadriven menu. Only problem, I don't know how to formulate my select statement. So here's the data
ID, TEXT HREF DESCR PARENTID
1 hMenu1 link1.htm desc text 0
2 hMenu2 link2.html desc text 0
3 sMenuM1 link3.htm desc text 1
4 sMenuTSMenu3 link4.htm desc text 3
I want the data structured so that below the row with id 1, would come it's subitems, subitems for the subitems and so on. So the recordset I'd like to return would in this case be:hMenu1 sMenuM1 sMenuTSMenu3 hMenu2 I've indented the data just to make things clearer. And finally, I want the substructure to be infinite at least in theory.I also do not want to use recursive logic to do this, if possible. I can change the structure of my table if neeed.Cheers!/Eskil
We have a requirement where in we need to sort the data after exporting to excel .But when we are trying to apply the filter on the excel and trying to sort we are getting an error ..
Has anyone ever looked at the way the grid data viewer sorts it data when clicking on a column header? If you click on a column header, something happens to the sorting of the data in the viewer, but it's not always clear to me _what_ is happening. It appears the data is sorted ascending on the column you clicked on. If you click again the sort order seems to inverse to descending. However, if you look closely, it turns out that the data is not always sorted correctly, especially when you click on an integer valued column.
I found this when doing a demo on the AdventureWorks demo extracting data from the SalesOrderDetail table. If you sort on the OrderQty column, data is correctly sorted in ascending quantities. However, if you click the column again, orders with an order quantity of 2 are displayed on top (while there are orders with a much higher order quantity) and if you scroll down the list, you notice that there is no clear sorting anymore. The same happens with other columns.
Is this supposed to work as I would expect it to do or is there a logical explanation for the behaviour I see?
-- Best regards,
Hans Geurtsen Docent Kenniscentrum
Info Support De Smalle Zijde 39 3903 LM Veenendaal The Netherlands www.infosupport.nl
I have a string filed which holds ID's such as: 1, 1.1, 1.2, 1.1.1, 1.1.2 I need to run a query to sort these by ID such as the result should be: 1, 1.1, 1.1.1, 1.1.2, 1.2 There could be infinite levels ie. 1.1.1.1.1, i'm baffled by this at the mo as i can't cast it to say a float and then sort because of the muliple points. Any ideas would be great. Looking for mimumum overhead here, would like to do it on the SQL Server side Thanks.
I've read aroud the following tecqnique to parameterize the sort criteria of a stored procedure:
PROCEDURE [ParamSortProc_EXAMPLE]
...
@SortField VARCHAR(64)
AS
BEGIN
SET NOCOUNT ON
-- ....
IF @SortField NOT IN ('Field1', 'Field2', 'Field3', 'Field4')
BEGIN
SET @SortField = 'Field1' -- Default
END
SELECT
-- ...
FROM
-- ...
WHERE
-- ...
ORDER BY
CASE @SortField WHEN 'Field1' THEN T.Field1 END ASC,
CASE @SortField WHEN 'Field2' THEN T.Field2 END ASC,
CASE @SortField WHEN 'Field3' THEN T.Field3 END DESC,
CASE @SortField WHEN 'Field4' THEN T.Field4 END DESC
The point is: the sort field is choosed based on the actual value of @SortField.
Provided the source table T has specific indexes for all the 4 fields involved into the sorting operation, does the engine actually benefit from it or, instead, treat the 4 sort conditions as computed fields and so build a temporary index to face with it?
I am using SQL Server 2000. I am getting the following error whenexecuting the following query. The query joins a view[Server2].dbx.dbo.vwreports that resides on a linked server. I can sorton fields on D table or R view individually, but some fields take along time to sort even they are on non-cluster indexes. I can not sorton any field in table E, K, and L at all. Also I tried to save thisquery as a view in the local server. SQL Server does not allowcreating a view base on this query.Does anyone know why?===== Error here =======Microsoft OLE DB Provider for SQL Server error '80040e14'Only text pointers are allowed in work tables, never text, ntext, orimage columns. The query processor produced a query plan that requireda text, ntext, or image column in a work table.===== Query here =======SELECT D.*, DATEADD(s, R.open_date, '12/31/1969 08:00:00pm') ASopenDate, DATEADD(s, R.close_date, '12/31/1969 08:00:00pm') AScloseDate, R.c_last_name AS custLastName, R.c_first_name AScustFirstName, R.description, (DATEDIFF(s, DATEADD(s, R.open_date,'12/31/1969 08:00:00pm'), getdate()))/(60*60*24) as lapse, E.[LASTNAME] AS techLastName, E.[FIRST NAME] AS techFirstName, E.EMPLOYEEID AStechID, K.MAKE, K.MAKEID, L.MODEL, L.MODELID, R.customer AS customer,R.iorg_name AS org FROM dbo.DellIDI D LEFT OUTER JOIN dbo.EMPLOYEE E onD.technicianid = E.employeeid LEFT OUTER JOIN dbo.MAIN M ON D.Serial =M.[S/N] LEFT OUTER JOIN dbo.MAKE K ON M.MAKE = K.MAKEID LEFT OUTER JOINdbo.MODEL L ON M.MODEL = L.MODELID INNER JOIN[Server2].dbx.dbo.vwreports R ON D.RequestID = R.ref_num order by(DATEDIFF(s, DATEADD(s, R.open_date, '12/31/1969 08:00:00pm'),getdate()))/(60*60*24)
akanksha shukla writes "the issue is that i want to sort the records stored in a table according to aparticular column in such a manner that the sorted reaults are permanantly commited onto the same table i.e. the results are reflected on the same table whose records are being used for sorting i know that order by clause can provide me a view of records sorted on basis of a particular column but the changes are not being reflected onto the table . i donot want to create a fresh table to reflect the changes. kindly suggest some help"
USE [Testing] GO /****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
INSERT INTO #Activities VALUES('Field phase S14-04932-01') INSERT INTO #Activities VALUES('Phase reporting') INSERT INTO #Activities VALUES('Phase running') INSERT INTO #Activities VALUES('RD1')
Ok this is what I have $query = $query = "SELECT date, file name FROM comment WHERE file name ='', AND CorS='C', AND Approved='Y', ORDER BY file name DESC";
But what I want is to return all results based on the file name from the most recent to the oldest. Can anyone help????
here's a good one for you...I want to return the last 20 records I have modified. I have adatemodified field - excellent.So I run a query to select the top 20 when ordered by datemodifieddesc.But now I have these results I want them sorted by companyname.Is this possible?Yes I could use my GUI to do the second sort, but can it be done justin a query?ThanksTim
I have a gridview that has AllowSorting="true" however I need to implement my own sorting because I have DateTime and Integer data types in several of the columns and I don't want an int column sorted like 1,12,2,23,3,34,4,45,5,56, etc. So, I've added SortParameterName="sortBy" and adjusted my stored procedure to accept this. For only ASC sorting, I've got ORDER BY CASE WHEN @sortBy='' THEN DateCreated END, CASE WHEN @sortBy='DateCreated' THEN DateCreated END and so on. However, columns can also be sorted with DESC. I tried CASE WHEN @sortBy='DateCreated DESC' THEN DateCreated DESC END, but I get a syntax error on DESC. How can I do this?
I am trying to set up custom paging and sorting with my gridview. All is well but the sorting. The problem is with the stored procedure. If I pass in the value @sortExpression as, for example "discussions_Posts.post_time", i does not sort it at all. But if I replace the @sortExpression with discussions_Posts.post_time in the actual stored procedure, it gets sorted. how do I sort this query with a input parameter with values like "discussions_Topics.topic_title" or something? ALTER PROCEDURE discussions_GetTopicsSubSet@startRowIndex as int,@maximumRows as int,@sortExpression as nvarchar(50),@board_id as intASDECLARE @Topics TABLE(RowNumber INT,topic_id INT,topic_title VARCHAR(50),topic_replies INT,topic_views INT,topic_type INT,topic_time DATETIME,post_id int,post_time DATETIME,Topic_Author_UserName nvarchar(256),Topic_Author_ID uniqueidentifier,Post_Author_Username nvarchar(256),Post_Author_ID uniqueidentifier)--DECLARE @TopicsFrom Datetime--SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END-- populate the table CAST(getdate() as int)INSERT INTO @TopicsSELECT ROW_NUMBER() OVER (ORDER BY @sortExpression), discussions_Topics.topic_id, discussions_Topics.topic_title, discussions_Topics.topic_replies, discussions_Topics.topic_views, discussions_Topics.topic_type,discussions_Topics.topic_time, discussions_Posts.post_id, discussions_Posts.post_time, user_1.UserName AS Topic_Author_Username, user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM discussions_Topics INNER JOIN discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_idWHERE (discussions_Topics.board_id = @board_id ANDdiscussions_Topics.topic_type NOT LIKE '1' )SELECT * from @TopicsWHERE RowNumber BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it. I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).
Hi, I hope I can explain what I am trying to achieve (in MS SQL Server): The results set that shows Names and Dates (plus others, but not relevant here), that needs to be sorted by Date and then by name. I have tried: ORDER BY date, surname, forename (gives dates in right order but not names) and ORDER BY surname, forename, date (gives names in right order but not dates)
I noticed when looking at the execution path that the SQL sort (order by) was taking up 16% of the total for the query. However the execution time for the sort was like .007 ms in SQL so I doubt it was really that much overhead.
Is it more effecient to sort a dataset within my asp.net program?
i have a simple table with NAME and a SORTORDER. Now i want to sort my table on the NAME desc and then insert a number into the SORTORDER. The number has to correspond with the number the NAME has in the list.
EXAMPLE
Initial situation: BAKKER 0 AKKER 0 CARNIE 0
Now my question is how can i update the table with a sp in wich te outcome is:
