Find maximum value from a record
-
VB.net / sql Consider a table having A - Z fields storing integer values. Therefore each record in this table will have A - Z fields. I need to find the maximum value for each record from the fields A - Z. Can you plz help me with this. With Best Regards, Mayur
-
VB.net / sql Consider a table having A - Z fields storing integer values. Therefore each record in this table will have A - Z fields. I need to find the maximum value for each record from the fields A - Z. Can you plz help me with this. With Best Regards, Mayur
You can use SQL to get the maximum of each field by doing something like this:
SELECT MAX(A) as MaxA, MAX(B) as MaxB, ...I've never tried it but maybe you can do an inner select that would look something like this:SELECT MAX( SELECT MAX(A) as MaxA, MAX(B) as MaxB,...)But if not, you could at least use the first one and then put all the values you get back into a datatable/arraylist and sort them to get the max. Hope this helps a little. -
You can use SQL to get the maximum of each field by doing something like this:
SELECT MAX(A) as MaxA, MAX(B) as MaxB, ...I've never tried it but maybe you can do an inner select that would look something like this:SELECT MAX( SELECT MAX(A) as MaxA, MAX(B) as MaxB,...)But if not, you could at least use the first one and then put all the values you get back into a datatable/arraylist and sort them to get the max. Hope this helps a little.Thnx Kschuler. the 1st query will give us the max value for each field. Also, the 2nd query, I am not sure if it works, may give us just one value. wat i neede is the max value of all the fields for each record. right now i am getting each field (every record) in an array, sorting it and getting the highest value from it. see the code below. myrow = DS.Tables("psrec").Rows(0) '1 record - 24 fields Dim myIntArray(24) As Integer For i = 0 To 23 myIntArray(i) = myrow(i) Next Array.Sort(myIntArray) maxunits = myIntArray(24) wat leaves me stumped is that since the array starts from 0, it should end at 23. However, the maxvalue can be found at myIntArray(24). when i print the array using the code, For i = 0 To 23 Console.WriteLine(myIntArray(i)) Next I always get 0 for myIntArray(0) for every record and the actual values are displayed from myIntArray(1). Therefore, one value is missed since the array prints till myIntArray(23). The last value is at myIntArray(24). Can you explain this to me... With Best Regards, Mayur
-
Thnx Kschuler. the 1st query will give us the max value for each field. Also, the 2nd query, I am not sure if it works, may give us just one value. wat i neede is the max value of all the fields for each record. right now i am getting each field (every record) in an array, sorting it and getting the highest value from it. see the code below. myrow = DS.Tables("psrec").Rows(0) '1 record - 24 fields Dim myIntArray(24) As Integer For i = 0 To 23 myIntArray(i) = myrow(i) Next Array.Sort(myIntArray) maxunits = myIntArray(24) wat leaves me stumped is that since the array starts from 0, it should end at 23. However, the maxvalue can be found at myIntArray(24). when i print the array using the code, For i = 0 To 23 Console.WriteLine(myIntArray(i)) Next I always get 0 for myIntArray(0) for every record and the actual values are displayed from myIntArray(1). Therefore, one value is missed since the array prints till myIntArray(23). The last value is at myIntArray(24). Can you explain this to me... With Best Regards, Mayur
Okay. I think I got it now. I tested this out and it seems to work, hopefully it will for you too:
SELECT MAX(A, B, C, D) FROM myTableThis will return the largest value for each record. Then you won't have to worry about doing it in code. As to why your array code wasn't working. I believe that when you declare your array as myIntArray(24) it doesn't create an array with elements of index 0 through 23. It really creates an array of elements 0 through 24. This means that you are not setting that last one, and perhaps it is defaulting to zero. Which would explain that after you sort it, the 0th element is always zero.