min/max inconsisten behavior - 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

SQL Server 2008 Forums

Transact-SQL (2008)

min/max inconsisten behavior

Author

Topic

stevenandler
Starting Member

42 Posts

Posted - 2014-04-09 : 15:56:30

The have the following T-SQL to return either all the max or min in a table:
SELECT A.GCN_SEQNO, A.SUBST_INSTRUCTIONS FROM OGEN.NDC_M_FORMULARY A
INNER JOIN
(
SELECT GCN_SEQNO, MAX(SUBST_INSTRUCTIONS) LATEST_SUBST
FROM OGEN.NDC_M_FORMULARY
group by GCN_SEQNO
) ABC
ON A.SUBST_INSTRUCTIONS = ABC.LATEST_SUBST AND A.GCN_SEQNO = ABC.GCN_SEQNO
WHERE FACILITY_KEY = 'ACCU' AND A.GCN_SEQNO = 18370
GROUP BY A.GCN_SEQNO , A.SUBST_INSTRUCTIONS ,ABC.LATEST_SUBST
THE ABOVE QUERY RETURNS
GCN_SEQNO SUBST_INSTRUCTIONS
18370 Mupirocin Ointment

If I specify MIN instead of MAX then nothing returns. Can someone tell me why that is?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-10 : 01:36:38

add this in the Inner Query

WHERE FACILITY_KEY = 'ACCU' AND A.GCN_SEQNO = 18370


SELECT A.GCN_SEQNO, A.SUBST_INSTRUCTIONS FROM OGEN.NDC_M_FORMULARY A
INNER JOIN
(
SELECT GCN_SEQNO, MAX(SUBST_INSTRUCTIONS) LATEST_SUBST
FROM OGEN.NDC_M_FORMULARY 
WHERE FACILITY_KEY = 'ACCU' AND A.GCN_SEQNO = 18370
group by GCN_SEQNO
) ABC
ON A.SUBST_INSTRUCTIONS = ABC.LATEST_SUBST AND A.GCN_SEQNO = ABC.GCN_SEQNO
WHERE FACILITY_KEY = 'ACCU' AND A.GCN_SEQNO = 18370
GROUP BY A.GCN_SEQNO , A.SUBST_INSTRUCTIONS ,ABC.LATEST_SUBST

sabinWeb MCP

stevenandler
Starting Member

42 Posts

Posted - 2014-04-10 : 12:01:32

I ADDED THE WHERE CLAUSE AND NOW IT RETURNS ONE LINE. THE PROBLEM IS, IT RETUNS THE MAX AND NOT THE MIN. WHAT I MEAN IS, IF I HAVE TWO ROWS AND THE SUBST_INSTRUCTIONS IN ONE HAS ABCD AND THE OTHER IS NULL, I WOULD HAVE THOUGHT THE MIN WOULD HAVE RETURNED THE ROW WHERE THE SUBST_INSTRUCTIONS IS NULL.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-11 : 08:47:37

From

http://technet.microsoft.com/en-us/library/ms179916.aspx

Remarks
MIN ignores any null values.
With character data columns, MIN finds the value that is lowest in the sort sequence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources