CODEWITHSUNDEEP
sqlsql-serversql-scripts
Max Schwartz
Max Schwartz

Reputation: 35

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!!!

Upvotes: 3

Views: 51

Answers (2)

Conorou
Conorou

Reputation: 31

Another way to do this:

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

Upvotes: 0

juergen d
juergen d

Reputation: 204756

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

Upvotes: 3

Related Questions