How to select specific records of groups based on criteria

Question

I'm trying to group a set of data and for some of the fields I need to select a specific value based on the ttype, for example I have the following rows:

caseid  age   iss   gcs    ttype
00170   64    25    17     Transfer Out
00170   64    27    15     Transfer In
00201   24    14    40     Transfer In

If a caseID has ttype 'Transfer Out' I want to use the ISS and GCS values from this row, otherwise use the values from the 'Transfer In' row.

My desired output based on the above example would be:

caseid  age  iss   gcs
00170   64   25    17
00201   24   14    40

My current select statement is:

select caseid, max(age), max(iss), max(gcs)
from Table1
group by caseid

Which I know is incorrect but how do I specify the values for ISS and GCS from a specific row?

Thanks

Edit - I will not always need to select from Row1, table below with expanded data:

caseid  age   iss   gcs  los  ttype          disdate
170     64    25    17   5    Transfer Out   2014-01-02 00:00:00.000
170     64    27    15   1    Transfer In    2014-01-04 00:00:00.000
201     24    14    40   4    Transfer In    2014-01-04 00:00:00.000

In this case, I want the max age and the ISS and GCS figure for row1 as before but I need to sum the LOS and select the disdate for row 2 (ie the latest date), so my output would be:

caseid  age   iss   gcs  los  disdate
170     64    25    17   6    2014-01-04
201     24    14    40   4    2014-01-04

Is this possible?

maybe this can help you http://stackoverflow.com/questions/63447/how-to-perform-an-if-then-in-an-sql-select — Cosmin, Sep 01 '15 at 13:20

Tim Schmelter · Accepted Answer · 2015-09-02T07:28:20.290

2

You can use a CTE and ROW_NUMBER + Over-clause (edited acc. to your updated question):

WITH CTE AS
(
   SELECT caseid,  age,  iss, gcs, los, ttype, disdate,
          SumLos = SUM(los) OVER (PARTITION BY caseid),
          LatestDisDate = MAX(disdate) OVER (PARTITION BY caseid),
          rn = ROW_NUMBER() OVER (PARTITION BY caseid 
                                  ORDER BY CASE WHEN ttype = 'Transfer Out' 
                                       THEN 0 ELSE 1 END ASC, disdate ASC)
   FROM dbo.Table1
)
SELECT caseid, age,  iss,  gcs, los = SumLos, disdate = LatestDisDate
FROM CTE
WHERE rn = 1

Demo

edited Sep 02 '15 at 07:28

answered Sep 01 '15 at 13:20

Tim Schmelter

450,073
74
686
939

Could you please explain what this does? - ORDER BY CASE WHEN ttype = 'Transfer Out' THEN 0 ELSE 1 END ASC – Reeya Oberoi Sep 01 '15 at 13:54
@ReeyaOberoi: the `ROW_NUMBER` is a ranking function, the `Partition By` is similar to a `Group By` so `caseid` is used as key of the groups. The `Order By` determines which row of the group gets which number. The consecutive numbers start at 1 and end at group-count. By using `CASE` i define that `ttype = 'Transfer Out'` is interpreted as 0 and all other as 1. This value is used for the ordering. On that way only one row with `ttype = 'Transfer Out'` get the number 1 which i filter later(`WHERE rn=1`). So only one row per `caseid`-group is returned. – Tim Schmelter Sep 01 '15 at 14:01
It's worth mentioning that `ROW_NUMBER` always generates unqiue numbers, so even if there are possibly multiple rows with `ttype = 'Transfer Out'` you get only one with `rn = 1`. That ensures that you only get one row per `caseid`-group. If that's not desired and you want to see all with `ttype = 'Transfer Out'`(if possible) replace `ROW_NUMBER` with `DENSE_RANK`. – Tim Schmelter Sep 01 '15 at 14:06
Thanks for this, I should have been clearer in my original query but I will not always need to select from Row1, I've updated my question with more details – GullitsMullet Sep 01 '15 at 15:24
@GullitsMullet: have a look – Tim Schmelter Sep 02 '15 at 07:28

score 0 · Answer 2 · answered Sep 01 '15 at 13:21

0

I think this is what you need -

;WITH CTE AS
(
SELECT case_id, age,iss,gcs, ROW_NUMBER () over (PARTITION BY ttype order by gcs DESC) Rn
from YOUR_TABLE_NAME
)
SELECT case_id,age,iss,gcs
from CTE where Rn =1

answered Sep 01 '15 at 13:21

Reeya Oberoi

813
5
19
42

How to select specific records of groups based on criteria

2 Answers2