Select rows based on other rows in same table

Question

In SQL Server, I have a table (let's call it TransList) that looks like:

TPN    Start    End    TDate                      DoneBy
10     6        7      2003-03-17 14:48:42.750    User2 *
10     1        6      2003-03-13 08:02:09.317    User3
11     3        6      2003-03-21 08:15:45.410    User3 ** yes
11     6        3      2003-03-13 08:13:13.920    User4 <--
11     5        6      2003-03-08 17:39:51.460    User4
12     13       3      2003-03-19 10:58:23.187    User8 *
12     6        13     2003-03-17 14:48:42.750    User7
12     3        6      2003-03-13 08:02:09.317    User6
12     1        3      2003-03-01 14:09:17.167    User1
13     3        6      2003-03-19 10:58:23.187    User1 *** no
13     1        3      2003-03-01 14:09:17.167    User2 <--
14     3        6      2003-03-21 08:15:45.410    User5 ** yes
14     13       3      2003-03-13 08:13:13.920    User6 <--
14     6        13     2003-03-08 17:39:51.460    User7
15     6        3      2003-03-17 14:48:42.750    User2 *
15     1        6      2003-03-13 08:02:09.317    User3

This is the result of a pretty complex query which joins two separate SELECT statements which joins several tables each. Rows are ordered by TPN ASC, TDate DESC.

I would now like to filter this table and obtain:

TPN    Start    End    TDate                      DoneBy
10     6        7      2003-03-17 14:48:42.750    User2
11     3        6      2003-03-21 08:15:45.410    User3
12     13       3      2003-03-19 10:58:23.187    User8
14     3        6      2003-03-21 08:15:45.410    User5
15     6        3      2003-03-17 14:48:42.750    User2

That is:

the newest transaction for each selected TPN
TPN is selected based on conditions on its newest transaction and/or its two newest transactions

Rows marked with * are there because (Start=6 and End=7) or (Start=13 and End=3) or (Start=6 and End=3) therefore I do not care of other transactions for these TPN

Rows marked with ** are there because (Start=3 and End=6) and for previous transaction (Start=6 and End=3)

Rows marked with *** are not there because (Start=3 and End=6) but for previous transaction is not (Start=6 and End=3)

I might need to further select based on which User did the previous transaction and/or have more complex logical conditions for * and ** (the state machine is complex and I have not yet finished checking it) but *** is always a not **.

I always need to check only the top 2 transactions for each TPN.

I am new to SQL and I spent already a couple of days to try figure out how I can achieve this. I considered self joining TransList, using LIMIT or TOP or walking through the table but I did not manage to make any of this solution work.

Can anyone help?

Edit:
The table above was an "extract", but to respond to the request of Andre, here is the query:

SELECT Items.[TPN],
       [StartStatus],
       [EndStatus],
       [TransactionDate],
       [TransDoneBy],
       [CurrentStatus],
       [Title],
       [Severity],
       [LastChangeDate],
       [ChangeDoneBy],
       [Planned],
       [Remaining]
FROM
(SELECT WORKITEM as 'TPN',
       TFIELDCHANGE.NEWSYSTEMOPTIONID as 'StartStatus',
       TFIELDCHANGE.OLDSYSTEMOPTIONID as 'EndStatus',
       THISTORYTRANSACTION.LASTEDIT as 'TransactionDate',
       trans_person.LASTNAME as 'TransDoneBy'
FROM dbo.THISTORYTRANSACTION JOIN dbo.TFIELDCHANGE ON (dbo.THISTORYTRANSACTION.OBJECTID = dbo.TFIELDCHANGE.HISTORYTRANSACTION)
                             JOIN dbo.TPERSON  trans_person ON (dbo.THISTORYTRANSACTION.CHANGEDBY = trans_person.PKEY)
WHERE dbo.TFIELDCHANGE.FIELDKEY = 4 -- Only transactions regarding status
) Transactions
JOIN
(SELECT WORKITEMKEY as 'TPN',
       TSTATE.LABEL as 'CurrentStatus',
       PACKAGESYNOPSYS as 'Title',
       TSEVERITY.LABEL as 'Severity',
       TWORKITEM.LASTEDIT as 'LastChangeDate',
       item_person.LASTNAME as 'ChangeDoneBy',
       max(CASE TATTRIBUTEVALUE.FIELDKEY WHEN 60 THEN TATTRIBUTEVALUE.INTEGERVALUE END) as 'Planned',
       max(CASE TATTRIBUTEVALUE.FIELDKEY WHEN 72 THEN TATTRIBUTEVALUE.INTEGERVALUE END) as 'Remaining'
FROM dbo.TWORKITEM JOIN dbo.TSTATE ON (dbo.TWORKITEM.STATE = dbo.TSTATE.PKEY)
                   JOIN dbo.TSEVERITY ON (dbo.TWORKITEM.SEVERITYKEY = dbo.TSEVERITY.PKEY)
                   JOIN dbo.TPERSON item_person ON (dbo.TWORKITEM.CHANGEDBY = item_person.PKEY)
                   JOIN dbo.TATTRIBUTEVALUE ON (dbo.TWORKITEM.WORKITEMKEY = dbo.TATTRIBUTEVALUE.WORKITEM)
WHERE dbo.TWORKITEM.STATE = 2 OR -- Current state: analyzed
      dbo.TWORKITEM.STATE = 3 OR -- Current state: assigned
      dbo.TWORKITEM.STATE = 4 OR -- Current state: suspended
      dbo.TWORKITEM.STATE = 6 OR -- Current state: implemented
      dbo.TWORKITEM.STATE = 7 OR -- Current state: verified
      dbo.TWORKITEM.STATE = 13   -- Current state: verifying
GROUP BY WORKITEMKEY,
         TSTATE.LABEL,
         PACKAGESYNOPSYS,
         TSEVERITY.LABEL,
         TWORKITEM.LASTEDIT,
         item_person.LASTNAME
) Items
ON Items.TPN = Transactions.TPN
ORDER BY Items.[TPN] ASC, [TransactionDate] DESC

Could you post the SQL query that gives you the result you posted above? — Simon Forsberg
– Simon Forsberg, Commented Jun 14, 2012 at 9:40
The paragraph starting "Rows marked with *..." is not even remotely clear. We haven't the foggiest idea what your Start and End really represent. You need to clarify the rule by which something should and should not appear. "Newest for each TPN" is simple enough but how should the system partition by Start and End? Is it that you want the latest TPN for each unique Start/End combination? — Thomas
– Thomas, Commented Jun 14, 2012 at 10:40
@Thomas Ok, sorry. I though to be clear enough, probably I was only lenghty. Let me retry. For each row in the table I want to check the most recent Start/End combination for each distinct TPN; if the Start/End combination matches a certain condition (e.g. Start=6 and End=3) then I need to check also the previous (in chronoligical order) combination for the same TPN before I can decide if I shall consider that TPN or not. If the TPN has to be considered, I will then select the row with the most recent Start/End combination. hope it is clearer but probably it is not... — rodedo
– rodedo, Commented Jun 14, 2012 at 11:09

Martin Smith · Accepted Answer · 2012-06-14 21:22:17Z

2

It seems you are looking for a general way to compare the latest transaction of a group with its predecessor.

This can be done using the ROW_NUMBER() function and a self join like this:

SELECT foo
FROM   (SELECT foo,
               Row_number() OVER (PARTITION BY TRN ORDER BY TDate) AS RN
        FROM   TranTable) AS Latest
       LEFT JOIN (SELECT foo,
                         Row_number() OVER (PARTITION BY TRN ORDER BY TDate) AS RN
                  FROM   TranTable) AS Previous
         ON Latest.RN = Previous.RN - 1
WHERE  Latest.RN = 1 /* Get only the latest */
       OR (/* your criteria for two latest */ AND Latest.RN IN (1,2))

edited Jun 14, 2012 at 21:22

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

answered Jun 14, 2012 at 17:13

Thomas Kejser

1,3241 gold badge10 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rodedo Over a year ago

I am trying this out; so far I have some issues, I will try some more and come back with more question. I assume that TRN shall be TPN according to my table, right?.

rodedo Over a year ago

I did not manage (yet) to use the suggested code as such, but it pointed me in the right direction and I now have an intermedaite step that will do for me. therefore upvoted and selected as answer, thanks!

Thomas Kejser Over a year ago

Thanks Rodedo. Glad to be of assistance.

Collectives™ on Stack Overflow

Select rows based on other rows in same table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related