MIN by case? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MIN by case?

Author

Topic

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-12 : 02:40:18

This will give me the Min term.
However it will only give me the IDs for which the statement is true.
How can i also display the IDs for which it is not.
I know its possible by Case or decode.

SELECT A.ID, min(A.TERM) as TERM
FROM TABLE A, TABLE B
                  WHERE A.ID = B.ID 
                  AND A.UNITED IS NOT NULL
                  AND B.CITY IS NULL            
GROUP BY A.ID
ORDER BY A.ID

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-12 : 04:58:35

Please post the DDL of the tables and give some sample data as well. It'll make us understand your problem better.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-12 : 05:09:14

By "statement"...do you mean the three conditions in the where clause??...
If you are looking for all ids from the first table...then removing the conditions will get you all ids from the first table.

If you're looking for all ids from both tables then...may be you're looking for a Full Outer Join. Check the link below:

[url]http://msdn.microsoft.com/en-us/library/ms187518.aspx[/url]

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-12 : 19:54:10

TABLE A

ID	TERM	UNITED
101452	604	LOP
101452	604	MA
999999	704	SC
999999	603	CI
999999	704	BPO
100013	603	LOS
100052	603	KIR
100071	603	SSK
100071	602	MNO

TABLE B

ID	CITY
101452	(null)
101452	(null)
999999	603
999999	603
999999	603
100013	(null)
100052	(null)
100071	(null)
100071	(null)

MY CODE GETS THIS:

ID	TERM	UNITED	CITY
101452	604	LOP	(null)
101452	604	MA	(null)
999999	704	SC	603
999999	603	CI	603
999999	704	BPO	603
100013	603	LOS	(null)
100052	603	KIR	(null)
100071	603	SSK	(null)
100071	602	MNO	(null)

I WANT:

ID	TERM
101452	604
999999	(null)
100013	603
100052	603
100071	602

So where there is a UNITED and NO CITY then display lowest TERM.
BUT IF there IS A UNITED AND CITY then NULL (eg ID 999999)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:42:21

[code]
SELECT ID,
NULLIF(MIN(TERM),0) AS TERM
FROM
(
SELECT A.ID, CASE WHEN A.UNITED IS NOT NULL AND B.CITY IS NULL THEN A.TERM ELSE 0 END as TERM
FROM TABLE A
INNER JOIN TABLE B
ON A.ID = B.ID
)t
GROUP BY ID
ORDER BY ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources