Sorting Data, ADO, SQL Server Etc...
Feb 21, 2008
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?
View 8 Replies
ADVERTISEMENT
Feb 11, 2007
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.
Any suggestions would help.
Thanks
View 1 Replies
View Related
Jul 10, 2015
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.
View 6 Replies
View Related
Apr 17, 2008
Hi,
I have a column containing data
3 KB
16 KB
2 KB
4 KB
43 KB
How to sort this column and display results like:
43
16
4
3
2
Thanks.
View 7 Replies
View Related
Jul 20, 2005
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.
View 4 Replies
View Related
Sep 12, 2001
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 ?
View 1 Replies
View Related
Apr 19, 2004
Hi there,
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:
ASP_Tin No
----------------
ss24fg
ss98jk
tp98ij
yh88ij
yh67tr
tp34ed
fg98bv
fg56sl
..........
and i need to sort it out into like this
ss tp yh fg
------------- ------------- ---------------- ---------------
ss24fg tp98ij yh88ij fg98bv
ss98jk tp34ed yh67tr fg56sl
i need to do it in sql statement which is in stored procedure
hope somebody can help me
Thanx
View 2 Replies
View Related
Sep 21, 2006
Dear all,
I have a field [Month] in my table AA.having Data like ( January,February,March,April etc.)
I need to sort them by starting of month
lets say as
January
February
March
How i can i do this
Have no index and can't define a index on this field
Thanks for ur time and Help
Regards
Mohd Sufian
View 12 Replies
View Related
Aug 16, 2002
Hi ,
Hopw can i sort the data in the ascending order so that i can see the date of my log files imported at the top of my table .
Right now my log files get imported and appended in the tables but they get appended in the descending order at the bottom of the last data .
Is there any way to sort the data so that i can see it in the reverse way when i open my tables ?
Many Thanks .
Anita.
View 1 Replies
View Related
Jul 23, 2005
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?
View 4 Replies
View Related
Jan 21, 2008
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.
View 6 Replies
View Related
May 31, 2007
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.
View 11 Replies
View Related
Apr 9, 2008
Hi All,
I want to sort the column data which is of positive and negative number like the following.
-5823
-1
200
100
i want to sort like
-1
100
200
-5823
How can i do that can any one help me..
Thanks in advance.
-john
View 10 Replies
View Related
Nov 13, 2007
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
View 7 Replies
View Related
May 11, 2007
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 ..
Is there any workaround for this??
View 1 Replies
View Related
Aug 28, 2007
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
View 1 Replies
View Related
Jul 23, 2007
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.
View 9 Replies
View Related
May 14, 2007
Hi All,
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?
Thanx a lot for your reply.
View 3 Replies
View Related
Jul 23, 2005
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)
View 1 Replies
View Related
Mar 24, 2006
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"
View 3 Replies
View Related
Apr 25, 2014
Sample Table
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.
View 7 Replies
View Related
Sep 24, 2015
I am trying to sort my sql resultset by an alphanumeric column of a table which is of NVARCHAR datatype. The sample data is given below:
CREATE TABLE #Activities(activityName NVARCHAR(100))
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')
[Code] ....
The output of the query is like this:
A1
A2
A3
A4
E1 0DAA1
E10
E2 0DAA2
[Code] .....
The output what I require is this:
A1
A2
A3
A4
E1 0DAA1
E2 0DAA2
[Code] ....
View 9 Replies
View Related
Apr 10, 2006
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????
View 7 Replies
View Related
Mar 27, 2008
hi,
i need a query based on sorting.
i have date column and time as varchar in another column and other order details
datecol timcol ordername type
12/31/2007 1:00 pm order1 1
12/31/2007 5:00 pm order2 1
12/22/2007 6:00 pm order3 1
so i need to get query o/p as
12/22/2007 6:00pm
12/31/2007 1:00 pm
12/31/2007 5:00pm
based on date and time. pls help!
View 6 Replies
View Related
Jun 27, 2006
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
View 3 Replies
View Related
Oct 11, 2006
I have a text field in the form of numbers separated by periods like this: 1,1.2,1.2.1
When I sort, it works fine unless I get out to 10 digits.
Desired sort: 1,1.1,1.1.1,1.1.2,.....,1.1.9,1.1.10
Actual sort: 1,1.1,1.1.1,1.1.10,1.1.2,.....,1.1.9
The 10 is showing up smaller than the 2 because of the straight text comparison, any ideas on how to get around this?
View 6 Replies
View Related
Aug 17, 2006
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?
View 2 Replies
View Related
Jun 25, 2007
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
View 1 Replies
View Related
Dec 13, 2007
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).
View 2 Replies
View Related
Jan 7, 2008
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)
Thanks for any advice!
View 2 Replies
View Related
Feb 26, 2004
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?
View 5 Replies
View Related
Aug 2, 2004
Hi,
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:
AKKER 1
BAKKER 2
CARNIE 3
Can someone please help me?
Greetings,
Mark
View 3 Replies
View Related
Aug 11, 2004
i have a question that is there any methods to order by the column which's datatype is ntext with t-sql?
View 1 Replies
View Related
