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
Go to Top of Page

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.
Go to Top of Page

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.

Go to Top of Page
   

- Advertisement -