15,031,510 members
1.00/5 (1 vote)
See more:
Need help on SQL query on checking whether comp available or not available from the below table. If level column contains 1 and if any below row is having level value as 2 then make as comp available or not available.

```ABC	      Level	 Check for Comp
1234Z2DD	0	Component Not available
1234Z2DD	1	Component Available
1234Z2DD	2
1234Z2DD	2
1234Z2DD	3
1234Z2DD	3
1234Z2DD	3
1234Z2DD	3
1234Z2DD	2
1234Z2DD	3
1234Z2DD	2
1234Z2DD	3
1234Z2DD	2
1234Z2DD	3
1234Z2DD	2
1234Z2DD	3
1234Z2DD	2
1234Z2DD	3
1234Z2DD	1	Component Available
1234Z2DD	2
1234Z2DD	3
1234Z2DD	2
1234Z2DD	3
1234Z2DD	2
1234Z2DD	2
1234Z2DD	3
1234Z2DD	1	Component Not available
1234Z2DD	1	Component Available
1234Z2DD	2	```

What I have tried:

tried with max and group by but not getting correct results
Posted
Updated 17-Mar-21 3:07am
Maciej Los 17-Mar-21 3:56am

What you mean by saying: "below row"?!?
Member 14936854 17-Mar-21 4:04am

then next row value of level 1
Maciej Los 17-Mar-21 4:05am

Is there any primary key?
Member 14936854 17-Mar-21 4:05am

if it is 2 then update as comp available for level 1

Solution 1

I have no idea what "below row" means...

Assuming that you want to find next or previous values in a table, you can use: LEAD[^] or LAG[^] function.

See:
SQL
```CREATE TABLE Components
(
ABC varchar(30),
[Level] int
);

INSERT INTO Components(ABC, [Level])
VALUES('1234Z2DD', 0), ('1234Z2DD',1),
('1234Z2DD',2), ('1234Z2DD',2),
('1234Z2DD',3), ('1234Z2DD',3),
('1234Z2DD',3), ('1234Z2DD',3),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',1), ('1234Z2DD',2),
('1234Z2DD',3), ('1234Z2DD',2),
('1234Z2DD',3), ('1234Z2DD',2),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',1), ('1234Z2DD',1),
('1234Z2DD',2);

SELECT ABC, [Level], Avail = CASE
WHEN [Level] = 0 THEN 'NA'
WHEN [Level] = 1 AND LEAD([Level]) OVER(ORDER BY ABC) > 1 THEN 'Available'
ELSE '' END
FROM COmponents;```

SQL Server 2019 | db<>fiddle[^]
v2
Member 14936854 17-Mar-21 9:07am

how to update column with above value i am getting error Windowed functions can only appear in the SELECT or ORDER BY clauses.
Maciej Los 17-Mar-21 9:26am

You wanted to know whether comp is available or not. Above query does it!
If you want to update existing field, you have to use UPDATE statement.