How to calc the greatest column value for each record?

[Mathew Butler]

Suppose I have a table t with columns id, col1, col2, col3, col4, col5, col6 all numeric. I want to query the table and for each value of col<x> in the resultset I want to identify the largest value in each of the columns. For example, with the following dataset:

id, col1, col2, col3, col4, col5, col6
1,2,3,4,5,6,7
2,8,6,5,4,3,2
3,2,3,9,4,2,1

I want a query to generate the following output:

1,7
2,8
3,9

In other databases I might use the "greatest" function eg: in mySQL;

select id, greatest( col1, col2, col3, col4, col5, col6 );

I've checked the help, googled and searched through a book to no avail. Is there a simple SQL based solution to this problem in MS access?

Thanks in advance,

Mat.

[Randy Harris]

* Mathew Butler:

Suppose I have a table t with columns id, col1, col2, col3, col4, col5,
col6 all numeric. I want to query the table and for each value of col<x>
in the resultset I want to identify the largest value in each of the
columns. For example, with the following dataset:

id, col1, col2, col3, col4, col5, col6
1,2,3,4,5,6,7
2,8,6,5,4,3,2
3,2,3,9,4,2,1

I want a query to generate the following output:

1,7
2,8
3,9

In other databases I might use the "greatest" function eg: in mySQL;

select id, greatest( col1, col2, col3, col4, col5, col6 );

I've checked the help, googled and searched through a book to no avail.
Is there a simple SQL based solution to this problem in MS access?

Thanks in advance,

Mat.

MAX is the SQL standard function.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[Bob Quintal]

"Mathew Butler" <ma***********@logicacmg.com> wrote in
news:11***************@ernani.logica.co.uk:

Suppose I have a table t with columns id, col1, col2, col3,
col4, col5, col6 all numeric. I want to query the table and
for each value of col<x> in the resultset I want to identify
the largest value in each of the columns. For example, with
the following dataset:

id, col1, col2, col3, col4, col5, col6
1,2,3,4,5,6,7
2,8,6,5,4,3,2
3,2,3,9,4,2,1

I want a query to generate the following output:

1,7
2,8
3,9

In other databases I might use the "greatest" function eg: in
mySQL;

select id, greatest( col1, col2, col3, col4, col5, col6 );

I've checked the help, googled and searched through a book to
no avail. Is there a simple SQL based solution to this problem
in MS access?

Thanks in advance,

Mat.

You could write a greatest() function, which is a kludge,
because like data is supposed to go into one column with
multiple rows, otherwise it's not normalized, or you could
create a union query of the ID, a subID, and the value, creating
a virtual table properly set up.

SELECT ID, 1 as COLID, col1 as theValue from table
UNION
SELECT ID, 2 as COLID, col2 from table
UNION
SELECT ID, 3 as COLID, col3 from table
UNION
SELECT ID, 4 as COLID, col4 from table
UNION
SELECT ID, 5 as COLID, col5 from table
UNION
SELECT ID, 16as COLID, col6 from table

Then use that as a source to a summation query

SELECT ID, COL, Max(theValue) from Query GROUP ON
ID, COLID

--
Bob Quintal

PA is y I've altered my email address.

[Randy Harris]

* Randy Harris:

* Mathew Butler:

Suppose I have a table t with columns id, col1, col2, col3, col4,
col5, col6 all numeric. I want to query the table and for each value
of col<x> in the resultset I want to identify the largest value in
each of the columns. For example, with the following dataset:

id, col1, col2, col3, col4, col5, col6
1,2,3,4,5,6,7
2,8,6,5,4,3,2
3,2,3,9,4,2,1

I want a query to generate the following output:

1,7
2,8
3,9

In other databases I might use the "greatest" function eg: in mySQL;

select id, greatest( col1, col2, col3, col4, col5, col6 );

I've checked the help, googled and searched through a book to no
avail. Is there a simple SQL based solution to this problem in MS access?

Thanks in advance,

Mat.

MAX is the SQL standard function.

Bob's right. I was careless with my reply. This is not trivial with SQL.
Best bet is to create you own "greatest" function.

Public Function Greatest(c1, c2, c3, c4, c5, c6)

Greatest = c1
If c2 > Greatest Then Greatest = c2
If c3 > Greatest Then Greatest = c3
If c4 > Greatest Then Greatest = c4
If c5 > Greatest Then Greatest = c5
If c6 > Greatest Then Greatest = c6

End Function

Since the fields are all numeric, you need not worry about nulls. Sorry
for the poor answer.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[Mathew Butler]

Many thanks.

I'd come to the similar conclusions, but I'd hoped for an existing function.

"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...

"Mathew Butler" <ma***********@logicacmg.com> wrote in
news:11***************@ernani.logica.co.uk:

Suppose I have a table t with columns id, col1, col2, col3,
col4, col5, col6 all numeric. I want to query the table and
for each value of col<x> in the resultset I want to identify
the largest value in each of the columns. For example, with
the following dataset:

id, col1, col2, col3, col4, col5, col6
1,2,3,4,5,6,7
2,8,6,5,4,3,2
3,2,3,9,4,2,1

I want a query to generate the following output:

1,7
2,8
3,9

In other databases I might use the "greatest" function eg: in
mySQL;

select id, greatest( col1, col2, col3, col4, col5, col6 );

I've checked the help, googled and searched through a book to
no avail. Is there a simple SQL based solution to this problem
in MS access?

Thanks in advance,

Mat.

You could write a greatest() function, which is a kludge,
because like data is supposed to go into one column with
multiple rows, otherwise it's not normalized, or you could
create a union query of the ID, a subID, and the value, creating
a virtual table properly set up.

SELECT ID, 1 as COLID, col1 as theValue from table
UNION
SELECT ID, 2 as COLID, col2 from table
UNION
SELECT ID, 3 as COLID, col3 from table
UNION
SELECT ID, 4 as COLID, col4 from table
UNION
SELECT ID, 5 as COLID, col5 from table
UNION
SELECT ID, 16as COLID, col6 from table

Then use that as a source to a summation query

SELECT ID, COL, Max(theValue) from Query GROUP ON
ID, COLID

--
Bob Quintal

PA is y I've altered my email address.

[Mathew Butler]

Very helpful - thanks.

I'd really hoped for an existing function - but you have provided an
impelmention - thanks for sharing!

"Randy Harris" <pl****@send.no.spam> wrote in message
news:6D*******************@newssvr12.news.prodigy. com...

* Randy Harris:

* Mathew Butler:

Suppose I have a table t with columns id, col1, col2, col3, col4,
col5, col6 all numeric. I want to query the table and for each value
of col<x> in the resultset I want to identify the largest value in
each of the columns. For example, with the following dataset:

id, col1, col2, col3, col4, col5, col6
1,2,3,4,5,6,7
2,8,6,5,4,3,2
3,2,3,9,4,2,1

I want a query to generate the following output:

1,7
2,8
3,9

In other databases I might use the "greatest" function eg: in mySQL;

select id, greatest( col1, col2, col3, col4, col5, col6 );

I've checked the help, googled and searched through a book to no
avail. Is there a simple SQL based solution to this problem in MS access?
Thanks in advance,

Mat.

MAX is the SQL standard function.

Bob's right. I was careless with my reply. This is not trivial with SQL.
Best bet is to create you own "greatest" function.

Public Function Greatest(c1, c2, c3, c4, c5, c6)

Greatest = c1
If c2 > Greatest Then Greatest = c2
If c3 > Greatest Then Greatest = c3
If c4 > Greatest Then Greatest = c4
If c5 > Greatest Then Greatest = c5
If c6 > Greatest Then Greatest = c6

End Function

Since the fields are all numeric, you need not worry about nulls. Sorry
for the poor answer.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[Rick Wannall]

I've included a function below that accepts a list punctuated by semi-colons and returns the largest value in the list. It seems to work fine with string values, integer values and non-integer values. A list contain numeric and non-numeric values is processed as a list of strings.

I don't know how practical it is for your situation, but it would work. It would require that you call the function in a query and concatenate the columns together as the argument to the function:

Select ID, GreatestInList(col1 & ";" & col2 & ";" & col3 & ";" & col4 & ";" & col5 & ";" & col6) As LargestValue

It doesn't matter how many columns you string together, or in what order, or anything else. As long as the list consists of values separated by semicolons it should work.

The function is shown below. Like I said, I don't know if this is a practical way for you, but it was an interesting challenge and wound up being not much code.

Public Function GreatestInList(ByVal varList As Variant) As Variant

Dim aList() As String
Dim l As Long
Dim greatest As Variant
Dim AllNumeric As Boolean
Dim AllInteger As Boolean

GreatestInList = Null
If IsNull(varList) = True Then
Exit Function
End If
If InStr(1, varList, ";") = 0 Then
GreatestInList = varList
Exit Function
End If
aList = Split(varList, ";")
greatest = aList(LBound(aList))
AllNumeric = True
AllInteger = True
For l = LBound(aList) To UBound(aList)
If IsNumeric(aList(l)) = False Then
AllNumeric = False
AllInteger = False
Exit For
End If
If AllNumeric = True Then
If InStr(1, aList(l), ".") = True Then
AllInteger = False
Exit For
End If
End If
Next l
For l = (LBound(aList) + 1) To UBound(aList)
If AllNumeric = False Then
'Since not all values are numeric, compare as strings
If aList(l) > greatest Then
greatest = aList(l)
End If
Else
If AllInteger = True Then
If CLng(aList(l)) > CLng(greatest) Then
greatest = aList(l)
End If
Else
If CDbl(aList(l)) > CDbl(greatest) Then
greatest = aList(l)
End If
End If
End If
Next l
GreatestInList = greatest
Exit Function

End Function

"Mathew Butler" <ma***********@logicacmg.com> wrote in message news:11***************@ernani.logica.co.uk...
Suppose I have a table t with columns id, col1, col2, col3, col4, col5, col6 all numeric. I want to query the table and for each value of col<x> in the resultset I want to identify the largest value in each of the columns. For example, with the following dataset:

id, col1, col2, col3, col4, col5, col6
1,2,3,4,5,6,7
2,8,6,5,4,3,2
3,2,3,9,4,2,1

I want a query to generate the following output:

1,7
2,8
3,9

In other databases I might use the "greatest" function eg: in mySQL;

select id, greatest( col1, col2, col3, col4, col5, col6 );

I've checked the help, googled and searched through a book to no avail. Is there a simple SQL based solution to this problem in MS access?

Thanks in advance,

Mat.

[Rick Wannall]

For consistency and reliability, this line:

If aList(l) > greatest Then

should have been written as this:

If cstr(aList(l)) > cstr(greatest) Then

I don't think it would fail for any reason without the cstr(), but since explicit is better than implicit any day, it should be in there.

"Rick Wannall" <cw******@yahoo.com> wrote in message news:Zf******************@newssvr12.news.prodigy.c om...
I've included a function below that accepts a list punctuated by semi-colons and returns the largest value in the list. It seems to work fine with string values, integer values and non-integer values. A list contain numeric and non-numeric values is processed as a list of strings.

I don't know how practical it is for your situation, but it would work. It would require that you call the function in a query and concatenate the columns together as the argument to the function:

Select ID, GreatestInList(col1 & ";" & col2 & ";" & col3 & ";" & col4 & ";" & col5 & ";" & col6) As LargestValue

It doesn't matter how many columns you string together, or in what order, or anything else. As long as the list consists of values separated by semicolons it should work.

The function is shown below. Like I said, I don't know if this is a practical way for you, but it was an interesting challenge and wound up being not much code.

Public Function GreatestInList(ByVal varList As Variant) As Variant

Dim aList() As String
Dim l As Long
Dim greatest As Variant
Dim AllNumeric As Boolean
Dim AllInteger As Boolean

GreatestInList = Null
If IsNull(varList) = True Then
Exit Function
End If
If InStr(1, varList, ";") = 0 Then
GreatestInList = varList
Exit Function
End If
aList = Split(varList, ";")
greatest = aList(LBound(aList))
AllNumeric = True
AllInteger = True
For l = LBound(aList) To UBound(aList)
If IsNumeric(aList(l)) = False Then
AllNumeric = False
AllInteger = False
Exit For
End If
If AllNumeric = True Then
If InStr(1, aList(l), ".") = True Then
AllInteger = False
Exit For
End If
End If
Next l
For l = (LBound(aList) + 1) To UBound(aList)
If AllNumeric = False Then
'Since not all values are numeric, compare as strings
If aList(l) > greatest Then
greatest = aList(l)
End If
Else
If AllInteger = True Then
If CLng(aList(l)) > CLng(greatest) Then
greatest = aList(l)
End If
Else
If CDbl(aList(l)) > CDbl(greatest) Then
greatest = aList(l)
End If
End If
End If
Next l
GreatestInList = greatest
Exit Function

End Function

"Mathew Butler" <ma***********@logicacmg.com> wrote in message news:11***************@ernani.logica.co.uk...
Suppose I have a table t with columns id, col1, col2, col3, col4, col5, col6 all numeric. I want to query the table and for each value of col<x> in the resultset I want to identify the largest value in each of the columns. For example, with the following dataset:

id, col1, col2, col3, col4, col5, col6
1,2,3,4,5,6,7
2,8,6,5,4,3,2
3,2,3,9,4,2,1

I want a query to generate the following output:

1,7
2,8
3,9

In other databases I might use the "greatest" function eg: in mySQL;

select id, greatest( col1, col2, col3, col4, col5, col6 );

I've checked the help, googled and searched through a book to no avail. Is there a simple SQL based solution to this problem in MS access?

Thanks in advance,

Mat.