Sorting Issue

Question

Post reply

Sorting Issue

DBABC

SSCarpal Tunnel

Points: 4813
More actions
February 12, 2013 at 1:49 pm

#289102

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*
FROMMyTransactions
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)
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 2

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").

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

U.T (2/12/2013)
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").

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

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 4

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

U.T (2/12/2013)
Attached are the current and required output screen shots. Note that i have created the required output in excel 🙂

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

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

FROMMyTransactions

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

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Lowell SSC Guru Points: 323518 More actions · Answer 7

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

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ant-Green SSC Guru Points: 113499 More actions · Answer 8

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

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 9

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
FROMMyTransactions
) 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.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

:w00t: You're welcome, but really don't use that for anything important..you will get bitten eventually 😎

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 11

mister.magoo (2/13/2013)
:w00t: You're welcome, but really don't use that for anything important..you will get bitten eventually 😎

Already seeing that 😀 but we understand the risk.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

Good to just know you are aware 😀

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 13

anthony.green (2/13/2013)
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

That's right, it is one I am writing. If you want to test it let me know and as soon as it is ready I will PM you about it.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);