MS SQL Server :: Sorting Out Data In A Field

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

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

SQL 2012 :: Accurate Sorting Data Each Time With Millions Of Records Without Time Field?

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

Field Not Sorting In Ascending Order

Dec 30, 2005

Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance

View 3 Replies View Related

Sorting Stored Procedure By Text Field

Mar 19, 2001

I want to sort a stored procedure based on a variable passed to it... what is the easiest way to do this? Here's an example of what I want to do:

sp_select_thing 10, 'thing_name', 'asc'

It would run the query then somehow evaluate parameters 2 and 3 to be placed in the ORDER BY clause. I'm not sure if they should be quoted as strings or not, I don't have an idea how to pass a "reference to a variable" as a parameter to a stored procedure... or even if such a thing is possible

Thanks!

View 1 Replies View Related

Selecting From Two Tables, And Sorting By Common Field.

Feb 10, 2002

This is feeling very hard for me, but is surely very easy for many of you.
I have 2 Tables. "Events" and "Meals". Both have a columns named "EventDate" and "EventTime". I need to be able to compile a list of both and sort by event date and time. For example, a Meal @ 5:30 would place itself between a 4:00 Event, and a 6:30 Event.

PLEASE HELP ME!!!
Pending deadline doom :(

Thanks,
kaskenasy@publishingconcepts.com

View 1 Replies View Related

Sorting Address Field By Its Component Parts

Nov 14, 2007

The file of about 30,000 records contains an address field where addresses are entered in the following form:

128 NW 8 ST
3444 SW 19 AVE
32 SE 122 TERR
12604 SW 148 PL

Then there are anomalies such as
123 E BLVD
(I can ignore the anomalies until the main challenge is overcome.)

The challenge is that I have been asked to sort the addresses in the following order:

quadrant (e.g., NW, SW),
street number (e.g., 122),
street name (e.g., TERR)
house number (e.g., 128)

It seemed like I should create a view that can be updated regularly, as records will be added/deleted ongoing.

How do I get these part into a four-column view? Or, is there another way to do this?

Esther

View 2 Replies View Related

Sorting On A Temporary Field With A Custom Paging Method

May 18, 2005

I've made another topic before concerning this problem, but since it was really confusing, I will made one clearer (it was about orthodromic formula, in case you read it, but the problem change during the topic, so thats why im creating this new one too).
I have a stored procedure with custom pagin method inside, and I want to sort my records on a fields I create myself (which will receive a different value for each record.) Now, I want to sort on this temporary field. And since this is a custom paging method I can choose between many page. Now, for the first page, it sorts fine. But when I choose a page above the first one, the sorting is not right (the results all are wrong).
So my real question is: is it really possible to sort on a Temporary Field in a custom paging method (because I know I can do it without any problem on a real field from my table, it just doesnt work right when I try on a temporary field). I tried to solve my problem with this little SQL instruction, but it didnt give me any result yet:
SELECT TOP 20 PK, test = field_value FROM Table WHERE PK not in (SELECT TOP 10 ad_id FROM Table ORDER BY ?) ORDER BY ?
well thanks for taking the time to read this, any help woulb be appreciated.

View 17 Replies View Related

Reporting Services :: Horizontal Axis Show Last Value In First And Last Space When Sorting A-z But Shows Correctly When Sorting Z-a

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

Sorting Data

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

Sorting Some Data...

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

Sorting Data Upon Import Into SQL 7

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

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 View Related

Sorting Data Having Months

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

Sorting The Data In Ascending Order .

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

Sorting Based On Various Data Types

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

Sorting Three Different Data Through One Column With 2 Values Only.

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

Problem Sorting Data Returned From Database

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

Problem With Sorting The Column Data On Table

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

SELECT And Then I'm Clueless. Ordering And Sorting Data

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

Sorting The Report Data After Exporting To Excel

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

Sorting Columns In Grid Data Viewer

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

Problem Importing Data From An Access Memo Field Into A SQL Server Ntext Field.

Jul 11, 2005

I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!

View 4 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

How To Matrix: Conditional Data-field Bg-color When Field Is Subtotal

Mar 25, 2008

How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???

View 3 Replies View Related

SQL Server 2008 :: Selecting Field That Contains Data From Another Field

May 28, 2015

We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.

ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY

what I need to do is identify where the Brand is in the Description field ...

I have tried ;

select * from Table
where Description Like Brand

not very successful.

View 3 Replies View Related

Importing Of Varchar Field Data In Number Field

Dec 5, 2007

i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?

jto it

View 5 Replies View Related

Grouping By One Field And Combine Data Of Another Field In SSRS

Apr 2, 2008

Hi all experters,

Please suggest how to build the report in below case:

Raw data:

ID
Member
Functions

1
Alan
A

1
Alan
B

2
Tom
A

2
Tom
B

2
Tom
C

3
Mary
D

3
Mary
E

Report Shows:

ID
Member
Functions

1
Alan
A,B

2
Tom
A,B,C

3
Mary
D,E

I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.

Any good suggestion?

Thanks in advance,
Steve Wang
2008/4/2

View 6 Replies View Related

Update SQL Field With Stripped Data From Other Field

May 12, 2006

Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin

View 1 Replies View Related

How To Convert To Regular Text, Data Stored In Image Data Type Field ????

Jul 20, 2005

Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD

View 1 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Transact SQL :: Return Field When A Field Contains Text From Another Field

Aug 25, 2015

I'm new to SQL and I'm trying to write a statement to satisfy the following:

If [Field1] contains text from [Field2] then return [Field3] as [Field4].

I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.

View 8 Replies View Related

How To Add Date Field And Time Field (not Datetime Field )

May 4, 2006

Good morning...

I begin with SQL, I would like to add a field that will be date like 21/01/2000.

Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.

How to do for having date and time in two different field.

Sorry for my english....

Cordially

A newbie

View 3 Replies View Related