🚀 go-pugleaf

Hi,

> How does one select a row in a table in which a column value is the
maximum
> value of all the rows ? The MAX(column_name) syntax does not appear that
it
> can be used in a WHERE clause but only in a SELECT clause to get the
maximum
> value itself. Instead I want all column values for a row where a
particular
> column has the maximum value. It seem as if the only way to do this is to
> first SELECT MAX(column_name) from ATABLE, and then use the value returned
> to to a SELECT * FROM ATABLE WHERE column_name = mymax_value .

Indeed you can't use MAX,MIN,AVG etc. in the WHERE clause, but you can use
them in the HAVING clause. Using having in your case doesn't seem the
resolution. You can use a sub-query in the WHERE clause or write a stored
procedure with the solution you suggest.

SELECT
  *
FROM
  ATable
WHERE
  ATable.AField = (SELECT MAX(AField) FROM ATable)

I'm afraid that the above query will be slow, so it's faster to use an SP.

Regards,
Arno

"Arno Brinkman" <bnewsgroups @ abvisie.nl> wrote in message
news:3de0d0f0$1@newsgroups.borland.com...
> Hi,
>
> > How does one select a row in a table in which a column value is the
> maximum
> > value of all the rows ? The MAX(column_name) syntax does not appear that
> it
> > can be used in a WHERE clause but only in a SELECT clause to get the
> maximum
> > value itself. Instead I want all column values for a row where a
> particular
> > column has the maximum value. It seem as if the only way to do this is
to
> > first SELECT MAX(column_name) from ATABLE, and then use the value
returned
> > to to a SELECT * FROM ATABLE WHERE column_name = mymax_value .
>
> Indeed you can't use MAX,MIN,AVG etc. in the WHERE clause, but you can use
> them in the HAVING clause. Using having in your case doesn't seem the
> resolution. You can use a sub-query in the WHERE clause or write a stored
> procedure with the solution you suggest.
>
> SELECT
>   *
> FROM
>   ATable
> WHERE
>   ATable.AField = (SELECT MAX(AField) FROM ATable)

Thanks, I managed to realize that this use of sub-query was thew answer.

>
> I'm afraid that the above query will be slow, so it's faster to use an SP.

OK, thanks for the hint to use a stored procedure instead.

In article <3de0d0f0$1@newsgroups.borland.com>, "Arno Brinkman" 
<bnewsgroups @ abvisie.nl> says...
> SELECT
>   *
> FROM
>   ATable
> WHERE
>   ATable.AField = (SELECT MAX(AField) FROM ATable)
> 
> I'm afraid that the above query will be slow, so it's faster to use an SP.
> 
	Shouldn't be a problem if there's a DESCENDING INDEX on 
ATable.AField.

	-Craig

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

"Craig Stuntz [TeamB]" <cstuntz@nospamplease.com (but use vertexsoftware to
email)> wrote in message
news:MPG.184c120210a6b55a98a253@newsgroups.borland.com...
> In article <3de0d0f0$1@newsgroups.borland.com>, "Arno Brinkman"
> <bnewsgroups @ abvisie.nl> says...
> > SELECT
> >   *
> > FROM
> >   ATable
> > WHERE
> >   ATable.AField = (SELECT MAX(AField) FROM ATable)
> >
> > I'm afraid that the above query will be slow, so it's faster to use an
SP.
> >
> Shouldn't be a problem if there's a DESCENDING INDEX on
> ATable.AField.

I don't see what the difference between a descending and ascending index is
in this situation. I think the key point is that there be an index so that
each row doesn't have to be read to find the maximum value.

In article <3de24925@newsgroups.borland.com>, eddielee@tropicsoft.com 
says...
> I don't see what the difference between a descending and ascending index is
> in this situation. I think the key point is that there be an index so that
> each row doesn't have to be read to find the maximum value.
> 
	IB can use a DESCENDING INDEX to find a MAX value, but it can't use 
an ASCENDING INDEX to find a MAX.  (Conversely, it can use an ASCENDING 
INDEX for a MIN value, but not a DESCENDING INDEX.)

	The reason is that IB prefix-compresses the index, and it would have 
to walk the entire index to get to the max node.  It would then have to 
determine if that row (or rows) was visible to the current transaction, 
and "back out" if not.

	Since you can create a DESCENDING INDEX rather easily, it's not a 
huge limitation.

	-Craig

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

"Craig Stuntz [TeamB]" <cstuntz@nospamplease.com (but use vertexsoftware to
email)> wrote in message
news:MPG.184c1b6154274d7b98a260@newsgroups.borland.com...
> In article <3de24925@newsgroups.borland.com>, eddielee@tropicsoft.com
> says...
> > I don't see what the difference between a descending and ascending index
is
> > in this situation. I think the key point is that there be an index so
that
> > each row doesn't have to be read to find the maximum value.
> >
> IB can use a DESCENDING INDEX to find a MAX value, but it can't use
> an ASCENDING INDEX to find a MAX.  (Conversely, it can use an ASCENDING
> INDEX for a MIN value, but not a DESCENDING INDEX.)
>
> The reason is that IB prefix-compresses the index, and it would have
> to walk the entire index to get to the max node.  It would then have to
> determine if that row (or rows) was visible to the current transaction,
> and "back out" if not.

If the index is sorted based on the index value, which seems pretty natural
so that a particular record can be found easily based on its index, then
going to the last record of the index of an ascending index will give the
max value.

In article <3de27571$1@newsgroups.borland.com>, eddielee@tropicsoft.com 
says...
> If the index is sorted based on the index value, which seems pretty natural
> so that a particular record can be found easily based on its index, then
> going to the last record of the index of an ascending index will give the
> max value.
> 
	Keep in mind that the last record pointed to in the index might not 
be visible to the current transaction.  In which case you are stuck with 
trying to walk the index backwards -- which is hard when we're talking 
about a prefix-compressed tree structure.

	-Craig

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

"Craig Stuntz [TeamB]" <cstuntz@nospamplease.com (but use vertexsoftware to
email)> wrote in message
news:MPG.184c493d33d5246b98a267@newsgroups.borland.com...
> In article <3de27571$1@newsgroups.borland.com>, eddielee@tropicsoft.com
> says...
> > If the index is sorted based on the index value, which seems pretty
natural
> > so that a particular record can be found easily based on its index, then
> > going to the last record of the index of an ascending index will give
the
> > max value.
> >
> Keep in mind that the last record pointed to in the index might not
> be visible to the current transaction.

If I am controlling the transaction, then I wouldn't be doing so by allowing
the index to be updated while I need to get the maximum value for my key
column. I will pass a transaction as part of my SQL statement that freezes
the table from being updated until I can get back my value.

In article <3de2bb5b$1@newsgroups.borland.com>, eddielee@tropicsoft.com 
says...
> If I am controlling the transaction, then I wouldn't be doing so by allowing
> the index to be updated while I need to get the maximum value for my key
> column. 

	The index doesn't need to be in the process of being updated.  Just 
the record.  IB is a versioning database.  There can be many records in 
the index which are not visible to your transaction.  Consider this 
situation:

o  Your transaction starts (SNAPSHOT)
o  Other guy's transaction starts
o  Other guy inserts record which is at top of index
o  You do a MAX
o  IB returns the maximum record visible to your transaction, *not* the 
maximum record in the index (which your transaction can't see).

> I will pass a transaction as part of my SQL statement that freezes
> the table from being updated until I can get back my value.
 
	This is possible, but it will quickly become a bottleneck in a 
heavily multiuser situation.

	-Craig

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer