Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Sorting Issue Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 1:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 31, 2014 12:50 PM
Points: 248, Visits: 477
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
Posted Tuesday, February 12, 2013 2:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 12,952, Visits: 32,476
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
Posted Tuesday, February 12, 2013 2:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 31, 2014 12:50 PM
Points: 248, Visits: 477
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
Posted Tuesday, February 12, 2013 2:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 13,320, Visits: 12,804
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)
Post #1419198
Posted Tuesday, February 12, 2013 2:47 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 31, 2014 12:50 PM
Points: 248, Visits: 477
Attached are the current and required output screen shots. Note that i have created the required output in excel

  Post Attachments 
CurrentOutput.JPG (4 views, 103.11 KB)
RequiredOutput.JPG (6 views, 117.17 KB)
Post #1419201
Posted Tuesday, February 12, 2013 4:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 13,320, Visits: 12,804
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)
Post #1419241
Posted Tuesday, February 12, 2013 5:20 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 1,816, Visits: 5,910
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


  • MMGrid Addin
  • MMNose Addin


  • 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



  •   Post Attachments 
    Sorted.png (73 views, 51.60 KB)
    Post #1419245
    Posted Wednesday, February 13, 2013 5:43 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 2:14 PM
    Points: 12,952, Visits: 32,476
    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
    Posted Wednesday, February 13, 2013 5:49 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Monday, December 8, 2014 1:45 AM
    Points: 5,221, Visits: 5,120
    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
    Posted Wednesday, February 13, 2013 7:35 AM
    SSC Veteran

    SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

    Group: General Forum Members
    Last Login: Friday, October 31, 2014 12:50 PM
    Points: 248, Visits: 477
    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 »

    Add to briefcase 12»»

    Permissions Expand / Collapse