Select top or max value from a field

[gk17]

I have two fields (VNUM and ANUM) which may either have duplicate values between them or one of them is a null field. I'm using isnull so it will definitely return one of the values in a single column instead of two separate columns.

SELECT DISTINCT PI.PIID, PI.SSN, PI.LastName, PI.FirstName, convert(varchar(10), PI.DOB, 101) as DOB, isnull(V.VNUM, V.ANUM) AS VA_NUM
FROM PI
	LEFT OUTER JOIN P
	ON PI.PIID = P.PIID
	LEFT OUTER JOIN V
	ON P.PID= V.PID
WHERE PI.SSN IS NULL
GROUP BY PI.SSN, PI.LastName, PI.FirstName, PI.DOB, V.VNUM, V.ANUM
ORDER BY PI.LastName

The problem is that some records will have multiple VNUM or ANUM values. I don't want multiple records returned. I want a distinct record returned for each name but want to show the top or max VNUM or ANUM value. It doesn't really matter which one as long as I get one value returned from one of those fields.

Thanks.

 

[gmmastros]

try this:

SELECT  PI.PIID, 
        PI.SSN, 
        PI.LastName, 
        PI.FirstName, 
        convert(varchar(10), PI.DOB, 101) as DOB, 
        SomeAliasName.NUM As VA_NUM
FROM    PI
        LEFT OUTER JOIN P
          ON PI.PIID = P.PIID
	LEFT OUTER JOIN (
           Select  PID,
                   Max(Case When VNUM > ANUM Then VNUM Else ANUM END) As NUM
           From    V
           Group By PID
           ) As SomeAliasName
           ON P.PID= SomeAliasName.PID
WHERE	PI.SSN IS NULL
ORDER BY PI.LastName

If this works, and you want me to explain it, please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk17]

Thanks George. I just tried it and think we're very close. I can confirm that the record count is accurate now but noticed that VA_NUM column shows NULL values for a lot of the records. Could this be due to the NULL values that are in one of the two columns?

V_NUM and A_NUM both may have NULL values in them but at least ONE of them should have an actual number in them. So even if one may be null (and it could be either one), the other should have a value. Does this change anything in the SQL query to make this work or should it still work even if one of those fields is NULL?

 

[gk53]

it could happens because VNUM > ANUM if one value is null will give you not right result... you compare null to number...
use something like

SELECT  PI.PIID, 
        PI.SSN, 
        PI.LastName, 
        PI.FirstName, 
        convert(varchar(10), PI.DOB, 101) as DOB, 
        SomeAliasName.NUM As VA_NUM
FROM    PI
        LEFT OUTER JOIN P
          ON PI.PIID = P.PIID
	LEFT OUTER JOIN (
           Select  PID,
                   Max(Case When isnull(VNUM, 0) > isnull(ANUM, 0) Then VNUM Else ANUM END) As NUM
           From    V
           Group By PID
           ) As SomeAliasName
           ON P.PID= SomeAliasName.PID
WHERE	PI.SSN IS NULL
        and SomeAliasName.NUM is not null
ORDER BY PI.LastName

 

[gk17]

Perfect. That corrected the problem. Thanks gk53.

 

[Olaf Doschke]

You could use COALESCE here:

Coalesce(ANUM,VNUM) as NUM

If you'd still have a NULL result, that would indicate both ANUM and VNUM are NULL, the case that never occurs. It won't break the query, if that happens nontheless. To avoid NULLs in all cases you could add a third default value, eg

Coalesce(ANUM,VNUM,-1) as NUM or Coalesce(ANUM,VNUM,999) as NUM

Bye, Olaf.

 

[gk17]

Olaf,

Do I use COALESCE with my original code and replace "isnull(V.VNUM, V.ANUM) AS VA_NUM" or do I incorporate it into George's query?

I tried using it with both and could only get it to run properly on my original code when I replaced the isnull. The result I get is the same as my original code though where it returns duplicate names with different V_NUM or A_NUM values.

 

[gk17]

Just want to clarify that this has already been resolved. I just wanted to try using COALESCE to see if that worked also. Thanks.

 

[Olaf Doschke]

You could put it instead of "Max(Case When isnull(VNUM, 0) > isnull(ANUM, 0) Then VNUM Else ANUM END) As NUM" in gk53 final solution.
It's less complicated than the CASE.

It will not return the MAX number of both, but as you initially said " doesn't really matter which one as long as I get one value returned from one of those fields." there's no reason to take the max of ANUM and VNUM.

Bye, Olaf.

 

[gk17]

I get the following errors when I try that:

Column 'VNUM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'ANUM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I added VNUM and ANUM to GROUP BY and the results returned were also the same as my original query where it showed duplicate results.

That's ok. I'll just stick with the solution provided by George/gk53.

Thanks for everyone's help.

 

[Olaf Doschke]

Oh, I se. My mistake to not see that GROUP BY. Make it "MAX(Coalesce(ANUM,VNUM)) as NUM" instead of "Max(Case When isnull(VNUM, 0) > isnull(ANUM, 0) Then VNUM Else ANUM END) As NUM", so keep the MAX for the aggregation.

Bye, Olaf.