Sorting Issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Sorting Issue

14 posts, Page 1 of 2

1 2 »»

Sorting Issue

Rate Topic

Display Mode

Topic Options

Author

Message

U.T

Posted Tuesday, February 12, 2013 1:49 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:35 PM
Points: 224, Visits: 407

I have some transactional data that i am having hard time to sort properly.

CREATE TABLE [MyTransactions](
[Id] [uniqueidentifier] NOT NULL Primary KEY,
[TType] [varchar](50) NULL,
[TTime] [datetime] NULL,
[TFunction] [varchar](50) NULL,
[TStatus] [varchar](50) NULL
)

DON'T CHANGE THE ORDER OF INSERT STATEMENTS BECAUSE THAT'S HOW WE ARE GETTING THE DATA.

INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:24:51.000', 'Activate', '')
INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:24:52.000', '', 'Activation Outgoing')

INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:25:00.000', 'Activate', '')
INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:25:00.000', '', 'Activation Outgoing')

INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:26:00.000', 'Deactivate', '')
INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:26:00.000', '', 'Deactivation Outgoing')

INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:27:17.000', 'Activate', '')
INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:27:17.000', 'Activate', '')
INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:27:17.000', '', 'Activation Outgoing')
INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:27:17.000', '', 'Activation Outgoing')

INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:29:17.000', 'Activate', '')
INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:29:17.000', 'Deactivate', '')
INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:29:17.000', '', 'Activation Outgoing')
INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:29:17.000', '', 'Dectivation Outgoing')

INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:30:00.000', 'Dectivate', '')
INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:31:00.000', 'Activate', '')
INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:31:00.000', '', 'Dectivation Outgoing')
INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:32:00.000', '', 'Activation Outgoing')

SELECT *
FROM MyTransactions
ORDER BY TTime, TType, TFunction

Above sort does not group the Incoming & Outgoing transactions together (see the attached currenoutput.jpg file). For each Activate/Deactivate function I want to group their Incoming/Outgoing transactions together.

So the required output should alway will be in this order (i am just showing two columns below to demonstrate the required sorting but actually i will need all columns in the output).

TFunction TType
Activate Incoming
Activate Outgoing
Activate Incoming
Activate Outgoing
Deactivate Incoming
Deactivate Outgoing

Please note that there is no additional information available in the table that can help grouping the data, also we cannot modify table structure so I want a query that can output the data in above order. (See the attached RequiredOutput.jpg)

Post Attachments

CurrentOutput.JPG (7 views, 103.11 KB)

RequiredOutput.JPG (3 views, 117.17 KB)

Post #1419176

Lowell

Posted Tuesday, February 12, 2013 2:15 PM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:41 PM
Points: 11,648, Visits: 27,760

UT excellent job with the DDL and sample data;

does this give you the results you are looking for?

SELECT
  ID,
  TTYpe,
  TTime,
  COALESCE(NULLIF(TFunction, ''), TStatus) AS FunctionStatus
FROM   MyTransactions
ORDER  BY
  TTime

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1419184

U.T

Posted Tuesday, February 12, 2013 2:26 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:35 PM
Points: 224, Visits: 407

Thanks Lowell. Unfortunately not, it still does not group them together.

There should always be an outgoing after an incoming, and we need to make sure both are of same function status (i.e. if incoming is of "Activation" the corresponding outgonig should be "Activation Outgoing").

Post #1419189

Sean Lange

Posted Tuesday, February 12, 2013 2:42 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

U.T (2/12/2013)

Can you post what the output order should be based on your sample data? (quite nicely posted I might add Wink

)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1419198

U.T

Posted Tuesday, February 12, 2013 2:47 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:35 PM
Points: 224, Visits: 407

Attached are the current and required output screen shots. Note that i have created the required output in excel Smile

Post Attachments

CurrentOutput.JPG (4 views, 103.11 KB)

RequiredOutput.JPG (6 views, 117.17 KB)

Post #1419201

Sean Lange

Posted Tuesday, February 12, 2013 4:58 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

U.T (2/12/2013)

Attached are the current and required output screen shots. Note that i have created the required output in excel Smile

I had a feeling that was what you were looking for. The problem is there is not any kind of sort that will work here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1419241

mister.magoo

Posted Tuesday, February 12, 2013 5:20 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 1,308, Visits: 3,899

Sean is right, however in this one case you can make it seem like it works....


SELECT *
FROM (
SELECT	*,row_number() OVER(PARTITION by TType ORDER BY TTime,TFunction,TStatus) AS rn
FROM	MyTransactions
) a
ORDER BY rn,TTime, TType, TFunction

Just don't use it because where you have multiple rows with the same time stamp there is no clear way to be sure of the order....

Post Attachments

Sorted.png (71 views, 51.60 KB)

Post #1419245

Lowell

Posted Wednesday, February 13, 2013 5:43 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:41 PM
Points: 11,648, Visits: 27,760

side note to mister.magoo: which SSMS add on is that that lets you group totals from the results grid?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1419436

anthony.green

Posted Wednesday, February 13, 2013 5:49 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Lowell (2/13/2013)

side note to mister.magoo: which SSMS add on is that that lets you group totals from the results grid?

I belive it is the custom one he is writing from this topic http://www.sqlservercentral.com/Forums/Topic1366484-391-1.aspx

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1419442

U.T

Posted Wednesday, February 13, 2013 7:35 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:35 PM
Points: 224, Visits: 407

mister.magoo (2/12/2013)

Sean is right, however in this one case you can make it seem like it works....


SELECT *
FROM (
SELECT	*,row_number() OVER(PARTITION by TType ORDER BY TTime,TFunction,TStatus) AS rn
FROM	MyTransactions
) a
ORDER BY rn,TTime, TType, TFunction

Just don't use it because where you have multiple rows with the same time stamp there is no clear way to be sure of the order....

Thanks mister.magoo that did the trick for what we are trying to achieve.

Post #1419515

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions