Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sorting Issue


Sorting Issue

Author
Message
DBABC
DBABC
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 482
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)
Attachments
CurrentOutput.JPG (7 views, 103.00 KB)
RequiredOutput.JPG (3 views, 117.00 KB)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38949
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
DBABC
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 482
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
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
DBABC
DBABC
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 482
Attached are the current and required output screen shots. Note that i have created the required output in excel :-)
Attachments
CurrentOutput.JPG (4 views, 103.00 KB)
RequiredOutput.JPG (6 views, 117.00 KB)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2268 Visits: 7824
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....



MM


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




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Attachments
    Sorted.png (73 views, 51.00 KB)
    Lowell
    Lowell
    SSChampion
    SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

    Group: General Forum Members
    Points: 14955 Visits: 38949
    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!

    anthony.green
    anthony.green
    SSCertifiable
    SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

    Group: General Forum Members
    Points: 6091 Visits: 6071
    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
    When a question, really isn't a question - Jeff Smith
    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


    DBABC
    DBABC
    SSC Veteran
    SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

    Group: General Forum Members
    Points: 249 Visits: 482
    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.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search