CODEWITHSUNDEEP
sqldatabasedb2
Diwakar Bhatt
Diwakar Bhatt

Reputation: 515

How to write SQL query based on complex conditions mentioned in DB2 Database

Table name is : RU_OUTPUT

Data is :

id  rudef   value   jobid   st_dt   
-----------------------------------------------------
8   23      200     32      1481585920      
9   24      2       32      1481989201      
12  24      3       32      1481547920      
10  26      50      32      1481544560      
13  26      89      32      1481545920      
14  24      69      32      1481548920  
15  23      56      32      1486899920
16  29      896     32      1486789920

Expected Result Summary: firstly we have to find all the rudef having count more than 1, after that for all those rudef we have to find the last and 2nd last record value as different column ONLY when lastvalue > secondlastvalue.

(last record will be assumed based on the id for that record i.e. for rudef '24' the last record will be where id = 14 and the second last where id = 12)

Expected Result

rudef   lastvalue  secondlastvalue
24      69         3
26      89         50

Note: Even though rudef '23' count is 2 but their lastvalue is 56 which is less than second last value 200, hence it will not be considered.

Upvotes: 0

Views: 122

Answers (3)

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Check below query working perfect for SQL server or Oracle.

        with CTE as
        (
        select * from (
        select * from (
        select *,row_number() over(partition by rudef order by r desc) as Rnk
        from
        ( select *,row_number() over(order by (select 1))  as R from RU_OUTPUT)an

        )a where Rnk < 3  )b  -- change 4 for find 3rd last
        pivot
        (
         max(value)for rnk in ([2],[1])
        )
        as s

        )
        select c1.rudef,max(c1.[1]) as 'lastvalue',
        max(c1.[2]) as 'secondlastvalue'  from CTE C1   
        group by c1.rudef
        having max(c1.[2]) is not null and max(c1.[1]) is not null
        and max(c1.[1]) > max(c1.[2])   -- here condtion is lastvalue > secondlastvalue you can change it also
        order by rudef

Using this query you can also calculate 3rd lastvalue and so on.

max(c1.[1]) = lastvalue
max(c1.[2]) = 2nd lastvalue
max(c1.[3]) = 3rd lastvalue 
   ............and so on

Let us know if you have any concerns.

Upvotes: 1

Utsav
Utsav

Reputation: 8093

Try this. But the logic with which you are rejecting 23, with same logic 26 will also not be in expected output. Anyways you can tweak internal queries to get the expected output. I tried in Oracle and it is giving expected output.

SELECT *
FROM
  ( SELECT rudef,
           max(value) AS lastvalue,
           min(value) AS secondlastvalue
   FROM
     ( SELECT t.*,
              row_number() over (partition BY rudef
                                 ORDER BY value DESC) AS val_rank
      FROM
        ( SELECT r.*,
                 row_number() over (partition BY rudef
                                    ORDER BY id DESC) AS rnk
         FROM RU_OUTPUT r) t
      WHERE rnk <=2 ) s
   WHERE rnk=val_rank
   GROUP BY rudef)
WHERE lastvalue<>secondlastvalue

Output

+-------+-----------+-----------------+
| RUDEF | LASTVALUE | SECONDLASTVALUE |
+-------+-----------+-----------------+
|    24 |        69 |               3 |
|    26 |        89 |              50 |
+-------+-----------+-----------------+

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

In most databases, you can use conditional aggregation and the ANSI standard row_number() function:

select rudef,
       max(case when seqnum = 1 then value end) as last_value,
       max(case when seqnum = 2 then value end) as second_last_value
from (select t.*,
             row_number() over (partition by rudef order by id desc) as seqnum
      from t
     ) t
group by rudef
having max(case when seqnum = 1 then value end) > max(case when seqnum = 2 then value end);

This assumes that id can be used to determine the last value. (Just change the order bys if another column should be used.)

I should note that you do not have to check for two values matching a rudef. If there is only one match, then the max() expression will be NULL and fail the having clauses.

Upvotes: 3

Related Questions