15,037,928 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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 140 Richard MacCutchan 65 BillWoodruff 30 Richard Deeming 25 CPallini 20
 OriginalGriff 3,195 Richard Deeming 1,848 Richard MacCutchan 1,670 CPallini 1,103 Dave Kreskowiak 781

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900