I have a Oracle table where data can be ordered on the basis of date. Now I have a request to get data to specific condition and previous row to that data. for example :

if I have

so i give condition Dept = '0' , it should retrun below 2 rows :

CodePudding user response：

This would give you all with dept 0 and it predecessors, but would not have duplicates

SELECT 
"Date", "Dept", "Employee" FROM tab1 WHERE "Dept" = 0
UNION
SELECT "Date", "Dept", "Employee" FROM tab1
WHERE "Date"  IN (SELECT "DAT"
FROM
(SELECT "Date","Dept",
 LAG("Date") OVER (ORDER BY
 "Date") dat FROM  tab1 ) t1
 WHERE "Dept" = 0
)

Date  | Dept | Employee
:---- | ---: | :-------
08/20 |    9 | Steve   
08/21 |    0 | Bella   

db<>fiddle here

CodePudding user response：

You may use a subquery to get the date of the required department and select the first two rows where date column is less than or equal to that department date. Try the following (Supposing that the date is unique among departments)

Select D.Date_, D.Dept, D.Employee
From tbl_name D
Where D.Date_ <= (Select Date_ From tbl_name Where Dept = 0)
Order By Date_ DESC
FETCH NEXT 2 ROWS ONLY;

See a demo from db<>fiddle.

If the date is not unique, you may choose an extra column to order by, i.e. employee column. In this case you may try the following:

With CTE As
(
  Select Date_, Dept, Employee, 
         ROW_NUMBER() Over (Order By Date_ DESC, Employee) rn
  From tbl_name
) 
Select Date_, Dept, Employee
From CTE
Where rn >= (Select rn From CTE Where Dept = 0)
FETCH NEXT 2 ROWS ONLY;

The second query is valid for both cases, see a demo.