Aggregation functon

Hi,
I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
And now I need
SELECT * FROM Table WHERE
row is equal to row which was used to calculate output of result from
SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
It suffices me this
SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
but it is not legal.
Thank for your suggestionsI think the easiest way to do this would be :
SELECT TOP 1 SQRT(X1*X2+Y1*Y2), *
FROM Table
ORDER BY SQRT(X1*X2+Y1*Y2) DESC
This will return you the first line only where SQRT() is the biggest. Mind
that if you want to have ALL lines where SQRT reaches it's maximum, then
you'll want this :
SELECT SQRT(X1*X2+Y1*Y2), *
FROM Table
WHERE SQRT(X1*X2+Y1*Y2) = (SELECT MAX(SQRT(X1*X2+Y1*Y2))
FROM Table)
Good luck
Roby
"B.J." wrote:

> Hi,
> I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> And now I need
> SELECT * FROM Table WHERE
> row is equal to row which was used to calculate output of result from
> SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> It suffices me this
> SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> but it is not legal.
> Thank for your suggestions|||something like this:
select *
from table
where pk = (select top 1 pk from table order by SQRT(X1*X2+Y1*Y2) desc)
where 'pk' is the table's primary key.
dean
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:67E9D409-A13A-4982-A7EA-74A946A42542@.microsoft.com...
> Hi,
> I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> And now I need
> SELECT * FROM Table WHERE
> row is equal to row which was used to calculate output of result from
> SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> It suffices me this
> SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> but it is not legal.
> Thank for your suggestions|||SELECT x1, x2, y1, y2, SQRT(x1*x2 + y1*y2)
FROM YourTable
WHERE x1*x2 + y1*y2 =
(SELECT MAX(x1*x2 + y1*y2)
FROM YourTable)
The SQRT() function is redundant in the subquery so I've left it out
here to save a few cycles.
David Portas
SQL Server MVP
--|||Another way to get all rows with the maximum X1*X2+Y1*Y2 is
SELECT TOP 1 WITH TIES SQRT(X1*X2+Y1*Y2), *
FROM T
ORDER BY SQRT(X1*X2+Y1*Y2) DESC
Steve Kass
Drew University
deroby wrote:
>I think the easiest way to do this would be :
>SELECT TOP 1 SQRT(X1*X2+Y1*Y2), *
> FROM Table
>ORDER BY SQRT(X1*X2+Y1*Y2) DESC
>This will return you the first line only where SQRT() is the biggest. Mind
>that if you want to have ALL lines where SQRT reaches it's maximum, then
>you'll want this :
>SELECT SQRT(X1*X2+Y1*Y2), *
> FROM Table
> WHERE SQRT(X1*X2+Y1*Y2) = (SELECT MAX(SQRT(X1*X2+Y1*Y2))
> FROM Table)
>Good luck
>Roby
>
>"B.J." wrote:
>
>