Max Schwartz Max Schwartz - 1 year ago 29
SQL Question

Need to create an 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 end up with this.

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


However, I only want one of these variables (the largest). Oh, and these variables are dates though I put them in simple numbers here for an easier read. They would be in this format:

YYYY-MM-DD HH:MM:SS


In the end... I want an output like this:

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


I imagine I would probably need a CASE statement to do this.
Also, I don't know if this helps but there are several other columns in my output but I only need to test to see if this variable has a NULL value.

(The DateDroppedOff is 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 YOU!!!

Answer Source

Generally 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download