Clever Geek Handbook

Query to filter records based on specific conditions

There is a_status_check table with the following data:

enter image description here

Requirement: if LC and BE status are present, then consider only LC. Otherwise, consider BE. Ignore other codes for this ID.

So, the result should look like this:

enter image description here

I tried the DECODE and CASE functions with no luck. Can someone help.

+3


source to share


4 answers


Use analytic functions:

select distinct
  id,
  first_value (status) over (partition by id order by status desc) status,
  first_value (amt   ) over (partition by id order by status desc) amt
from
  tq84_a_status_check
where
  status in ('LC', 'BE')
order by
  id;


Testdata:

create table tq84_a_status_check (
  id number,
  status varchar2(10),
  amt number
);

select distinct
  id,
  first_value (status) over (partition by id order by status desc) status,
  first_value (amt   ) over (partition by id order by status desc) amt
from
  tq84_a_status_check
where
  status in ('LC', 'BE')
order by
  id;
+1


source


You can try this:



SELECT id, status, amt
FROM yourTable yt
INNER JOIN (
        -- get only max
        SELECT id, MAX(status) as status
        FROM yourTable
        GROUP BY id
    ) as onlyMax
      ON yt.id = onlyMax.id
      AND yt.status = onlyMax.status
0


source


This solution should work if you only have two values STATUS

(A and B). First I get a temporary table containing the count STATUS

for each ID

and then I JOIN

back to a_status_check

. The proposal WHERE

saves the entries A

when they appear separately and saves the entries B

when A

and are B

displayed together.

SELECT * FROM a_status_check t1
INNER JOIN
(
    SELECT ID, COUNT(*) AS status_count
    FROM a_status_check
    GROUP BY ID
) t2
ON t1.ID = t2.ID
WHERE (t1.STATUS = 'A' AND t2.status_count = 1)
    OR (t1.STATUS = 'B' AND t2.status_count = 2);
0


source


try it

SELECT id, status, amt
FROM a_status_check t
INNER JOIN (
        SELECT id, MAX(status) as status
        FROM a_status_check
        GROUP BY id
    ) as a
      ON t.id = a.id
      AND t.status = a.status
0


source






More articles:


All Articles