Clever Geek Handbook

You need to create a SQL script to get this result

Let's say I have this:

+-----+------+
| ID  | Var  |
+-----+------+
| 100 | 2    |
| 100 | 4    |
| 100 | NULL |
+-----+------+
| 425 | 1    |
| 425 | 2    |
| 425 | 3    |
| 425 | 7    |
+-----+------+
| 467 | NULL |
| 467 | NULL |
+-----+------+
| 500 | 3    |
| 500 | NULL |
| 500 | NULL |
+-----+------+

If even one of these ids has a NULL Var associated with it, I need to remove all ids of that value from the script output. So I would be done with that.

+-----+------+
| ID  | Var  |
+-----+------+
| 425 | 1    |
| 425 | 2    |
| 425 | 3    |
| 425 | 7    |
+-----+------+

However, I only need one of these variables (the largest one). Oh, and these variables are dates, although I've put them in prime numbers here for easier reading. They will be in this format:

YYYY-MM-DD HH: MM: SS

In the end ... I need the output like this:

+-----+------+
| ID  | Var  |
+-----+------+
| 425 | 7    |
+-----+------+

I guess I probably need a CASE statement for this. Also, I don't know if this helps, but there are several other columns in my output, but I only need to check if this variable is NULL.

(DateDroppedOff - Var)

My current script (Slightly simplified to only have relevant information):

SELECT TOP 100000 [t1].[ID]
      ,[t1].[DateCreated]
      ,[t3].[DateDroppedOff]    
      ,[t3].[HasBeenDroppedOff]
      ,[t3].[ManifestItemID]
      ,[t3].[ManifestID]
FROM [t1]
LEFT JOIN t2 ON t1.ID = t2.ID
LEFT JOIN t3 ON t2.MovementID = t3.MovementsID
ORDER BY t1.ID

THANK!!!

+3


source to share


2 answers


Typically you can do it like this



select id, max(var)
from your_table
group by id
having sum(case when var is null then 1 else 0 end) = 0
+3


source


Another way to do it:



SELECT ID, MAX(VAR) as VAR 
FROM table A 
WHERE NOT EXISTS (SELECT 1 FROM table B WHERE A.ID = B.ID and B.VAR IS NULL) 
GROUP BY ID
0


source






More articles:


All Articles