Question on ANSI SQL syntax

[mjma]

A rather simplistic question - in the following SQL statement, is it standard ANSI SQL to be able to select the max(statementdate) for each record as shown below? or is that an "extension" allowed by the particular DBMS? (in this case Sybase's Transact SQL) I haven't come across this usage in my ANSI SQL books.

select
brokercompany, statementdate = max(statementdate)
   from dbo.brokeracctbalance
   group by brokercompany
   order by statementdate

Thanks!
Mark

 

[tlbroadbent]

MAX is standard in ANSI SQL along with the following aggregate functions - SUM, AVG, MIN, COUNT(*). Terry

http://members.home.net/tlbroadbent/index.htm

 

[mjma]

Terry,

The question was not whether MAX is a standard function, but the way it is used in the SQL statement I included. In other words, I have seen SQL like

select max(statementdate)

which would return the most recent statementdate, but I hadn't seen

select
brokercompany, statementdate = max(statementdate)
...
group by brokercompany

which gives the most recent statementdate for each brokercompany.

Mark

 

[MalcolmW]

Yes that is standard SQL. The exception is the use of the undeclared variable "statementdate". I think only Sybase and SQL Server allow undeclared variables, and variables are not part of ANSI SQL in any form.
Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[stoleru]

Hi mjma, try this query:

Select brokercompaby,max(statementdate) as statementdate
...
Group By brokercompany;
Eduard Stoleru
e_stoleru@yahoo.com

 

[mjma]

Thanks to both Malcolm for clarifying that variables aren't a part of standard ANSI SQL, and to Eduard for providing a way to rewrite the SQL ... I don't know why that works, but it does! If I write

Select brokercompany, max(statementdate) 
... 
Group by brokercompany;

without the as statementdate, then I get repeating rows. But with the as statementdate, I get just one row per brokercompany, which is what I want.