Finding the max value from one of three columns.

Results 1 to 2 of 2

Thread: Finding the max value from one of three columns.

  1. #1
    Join Date
    Dec 1969

    Default Finding the max value from one of three columns.

    Okay, it&#039;s easy to find the MAX from one column, but what if I wanted to compare THREE columns in one hit and obtain the maximum value?<BR><BR>I want to utilzie SQL to do this ...<BR><BR>Ie:<BR>Col1 = 3, Col2 = 6, Col3=1 Value = 6<BR>Col1 = 7, Col2 = 3, Col3=2 Value = 7<BR>etc<BR><BR>Is there a &#039;clean&#039; way to do this? Or simply a lengthy CASE statement?

  2. #2
    Join Date
    Dec 1969

    Default RE: Finding the max value from one of three column

    Since you "cannot perform an aggregate function on an expression containing an aggregate or a subquery," why don&#039;t you just use ADO to do the sorting instead of your SQL server?<BR><BR>In other words, just do your query and compare each field with a sorting method :<BR><BR>Do while Not recset.EOF<BR> maxSoFar = -1<BR> For each FldVal in recset.Fields<BR> If FldVal.value &#062; maxSoFar Then<BR> maxSoFar = FldVal.value<BR> End If<BR> Next<BR> &#039; -- now write out Max Row value or set an array index<BR> Response.Write maxSoFar & "&#060; br &#062;"<BR> recset.MoveNext<BR>Loop<BR>-----<BR><BR>I generally let the SQL server be my data store and let ADO/VB be my logic processing. I know that you might not want to load an entire recordset into memory and cycle through it, but for advanced searches like this one, I recommend to do it with ADO and get on with the next phase of your project. The above code should look familiar so it won&#039;t be any learning curve as well. Stick with what you know and don&#039;t get caught in any complicated SQL commands.<BR><BR>Separate your data store from your logic processing.<BR><BR>** Unless you are on your way to become a DBA, then ignore everything I said :-)<BR><BR>HtH,<BR><BR>Reginald Dawson

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts