Click here to Skip to main content
15,042,397 members
Please Sign up or sign in to vote.
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
Comments
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

1 solution

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
Comments
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)




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