How to compare rows with each other and keep only one row according to a condition – This article will take you through the common SQL errors that you might encounter while working with sql, sql-server, tsql. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I have a requirement as per below:
-
IF More than 1 comment exist (group of name, lastname and door) and one of them includes NULL then keep only the record with the Null comment and discard the others.
-
IF Null IS NOT one of them and the comment includes NOT AVAILABLE and REQUIRES. Keep Not available – discard REQUIRES
-
IF all of them have only REQUIRES choose the record with the lowest amount or value.
Name Lastname Comment Amount Door
John R. NULL 250 1
John R. NULL 250 1
John R. New design is available 250 1
John W. Not available 250 2
John W. Requires additional comment 450 2
John S. Requires further explanation 200 3
John S. Requires more information 300 3
Result should come like below:
Name Lastname Comment Amount Door
John R. NULL 250 1
John W. Not available 250 2
John S. Requires further explanation 200 3
I am trying to write a CTE to get the result but not sure how to compare the comment section something like below:
WITH RNs AS(
SELECT name,
lastname,
door,
package,
DENSE_RANK() OVER (PARTITION BY name ORDER BY door ASC) AS RN
FROM test)
SELECT distinct name,
lastname,
door,package,
CASE when package IS NULL THEN 'PASS'
when package like 'Not available%' then 'PASS'
when package like 'requires%' then 'PASS' else 'fail' END AS to_keep_or_not
FROM RNs
GROUP BY RN,
name,
lastname,
door,package;
What would be the best approach to solve this kind of problem statement?
Solution :
Please try below query:
with cte as (
select name ,lastname,comment,amount,door ,row_number()over(partition by name ,lastname order by comment ,(case when comment like '%Requires%' then amount else 0 end)) rn from testreq
where comment is null or comment like '%Not available%' or comment like '%Requires%')
select Name,lastname,comment,amount,door from cte where rn=1
order by door
Output:
|name|lastname|comment |amount|door|
|----|--------|----------------------------|------|----|
|John|R. |NULL |250 |1 |
|John|W. |Not available |250 |2 |
|John|S. |Requires further explanation|200 |3 |
You want an order by with a case expression . . . along with ROW_NUMBER():
SELECT t.*
FROM (SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY name, lastname
ORDER BY (CASE WHEN comment IS NULL THEN 1
WHEN comment LIKE '%NOT AVAILABLE%' THEN 2
ELSE 3
END),
amount
ORDER BY door ASC
) as seqnum
FROM test t
) t
WHERE seqnum = 1;
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.