Get the maximum value of a row

[mikki6971]

I have a recordset that is comprised of several columns of data. Most of the columns refer to a 15 min increment of each day. I need to be able to find the value that is used the most for each day. I am using ASP for the page and SQL to create the recordset.

Here is an example of data:

EFFDATE T_0000 T_0015 T_0030 T_0045 ..... T_1145
10/27/2008 ASW ASW BRK CAT ASW

For 10/27/2008, I need to return ASW, since it is the most used value. Any suggestions on how to achieve this?

Thank you in advance for any help with this issue.

[JamieHowarth0]

Hi there,

The better way of storing this data would be to create a single table which has the following columns - EFFDATE (as you currently use it), TIME (to store the 4-digit time you choose) and VALUE (to store ASW values).
In this way, you could use the following SQL code:

SELECT EFFDATE, COUNT(VALUE), VALUE FROM myTable
GROUP BY EFFDATE, VALUE
ORDER BY COUNT(VALUE)
 

This would then pull off the following:
[EFFDATE] | [Expr1000] | [VALUE]
10/27/2008 | 3 | ASW
10/27/2008 | 1 | BRK
10/27/2008 | 1 | CAT

If you changed the SELECT to SELECT TOP 1 you'd then get the maximum value, or just ASW, which is what appears to be your ideal solution.

Hope this helps.

codegecko

[mikki6971]

Thank you very much for your response. Unfortunately, I don't believe I would be able to change the actual table. Is there a way to create a dynamic table, using the data as it is currently stored, and converting it to the way you describe it? Which would only be used for my application? In the meantime, I will attempt to sway my boss, to allow the table to be changed, which is most likely a "NO!" : )

Thanks again!

[JamieHowarth0]

Hi there,

What database engine are you using - Access, Microsoft SQL Server, MySQL etc.?
If you're using Microsoft SQL Server 2005 there's a handy SQL function called PIVOT - or, as you've mentioned, creating a temporary table to hold the manipulated results, but I'd need to confirm it's SQL Server you're using to be able to produce some useful code.

codegecko

[mikki6971]

the database is oracle 10g.

thanks!!

[mikki6971]

i have been able to get the data into a string using the EFFDATE and values of each of the times when they are not null. Is there a way to count the different values and return the max? Here is the code and the output I have to this point:
--------------
while not rsTblEmpCalendar.EOF = true
for each myField in rsTblEmpCalendar.Fields
if myField.value <> "" then
strFieldValue = strFieldValue & myField.Value & ", "
end if
next
rsTblEmpCalendar.movenext
wend
--------------
10/27/2007, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, 1/5/2008, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, BRK, ASW, ASW, ASW, MTG, MTG, MTG, MTG, MTG, BRK, BRK, BRK, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, MTG, MTG, MTG, 10/15/2008, ASW, ASW, ASW, ASW, MTG, MTG, MTG, MTG, BRK, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, BRK, BRK, MTG, MTG, MTG, ASW, ASW, BRK, BRK, ASW, ASW, ASW, MTG, ASW, ASW, ASW, BRK, CAT, CAT, CAT, ASW, 10/21/2008, ASW, ASW, ASW, ASW, MTG, MTG, MTG, MTG, BRK, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, BRK, BRK, ASW, ASW, MTG, MTG, MTG, BRK, BRK, MTG, ASW, ASW, ASW, MTG, ASW, ASW, BRK, ASW, ASW, ASW, ASW, 10/23/2008, ASW, ASW, ASW, ASW, MTG, MTG, MTG, MTG, BRK, ASW, ASW, ASW, ASW, ASW, ASW, ASW, ASW, BRK, BRK, ASW, ASW, ASW, ASW, BRK, ASW, MTG, ASW, MTG, MTG, ASW, ASW, ASW, BRK, ASW, ASW, ASW, ASW,
--------------
Thank you for all of your help!!