469,323 Members | 1,468 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Get the maximum value of a row

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.
Oct 27 '08 #1
5 1424
JamieHowarth0
533 Expert 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT EFFDATE, COUNT(VALUE), VALUE FROM myTable
  2. GROUP BY EFFDATE, VALUE
  3. ORDER BY COUNT(VALUE)
  4.  
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
Oct 27 '08 #2
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!
Oct 27 '08 #3
JamieHowarth0
533 Expert 512MB
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
Oct 28 '08 #4
the database is oracle 10g.

thanks!!
Oct 28 '08 #5
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!!
Oct 28 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by KemperR | last post: by
2 posts views Thread by Kums | last post: by
6 posts views Thread by =?Utf-8?B?U2hhcm9u?= | last post: by
1 post views Thread by maurizio | last post: by
7 posts views Thread by laredotornado | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.