Sql Server How to use an aggregate function like MAX in a WHERE clause

Question

I want get the maximum value for this record  Please help me   SELECT rest field1      FROM mastertable AS m     INNER JOIN            SELECT t1 field1 field1                  t2 field2             FROM table1 AS T1              INNER JOIN table2 AS t2 ON t2 field   t1 field              WHERE t1 field3 MAX t1 field3          --                                  Help me here        AS rest ON rest field1   m field

User · Accepted Answer

You could use a sub query     WHERE t1 field3    SELECT MAX st1 field3  FROM table1 AS st1    But I would actually move this out of the where clause and into the join statement  as an AND for the ON clause

User · Answer

The correct way to use max in the having clause is by performing a self join first   select t1 a  t1 b  t1 c from table1 t1 join table1 t1 max   on t1 id   t1 max id group by t1 a  t1 b  t1 c having t1 date   max t1 max date    The following is how you would join with a subquery   select t1 a  t1 b  t1 c from table1 t1 where t1 date    select max t1 max date                   from table1 t1 max                  where t1 id   t1 max id    Be sure to create a single dataset before using an aggregate when dealing with a multi-table join   select t1 id  t1 date  t1 a  t1 b  t1 c into  dataset from table1 t1 join table2 t2   on t1 id   t2 id join table2 t3   on t1 id   t3 id   select a  b  c from  dataset d join  dataset d max   on d id   d max id having d date   max d max date  group by a  b  c   Sub query version   select t1 id  t1 date  t1 a  t1 b  t1 c into  dataset from table1 t1 join table2 t2   on t1 id   t2 id join table2 t3   on t1 id   t3 id   select a  b  c from  dataset d where d date    select max d max date                  from  dataset d max                 where d id   d max id

User · Answer

As you ve noticed  the WHERE clause doesn t allow you to use aggregates in it   That s what the HAVING clause is for   HAVING t1 field3 MAX t1 field3

User · Answer

But its still giving an error message in Query Builder  I am using SqlServerCe 2008   SELECT         Products Master ProductName  Order Products Quantity  Order Details TotalTax  Order Products Cost  Order Details Discount                        Order Details TotalPrice FROM           Order Products INNER JOIN                      Order Details ON Order Details OrderID   Order Products OrderID INNER JOIN                      Products Master ON Products Master ProductCode   Order Products ProductCode HAVING         Order Details OrderID    SELECT MAX OrderID  AS Expr1 FROM Order Details AS mx1     I replaced WHERE with HAVING as said by  powerlord  But still showing an error   Error parsing the query   Token line number   1  Token line offset   371  Token in error   SELECT

User · Answer

SELECT rest field1 FROM mastertable as m INNER JOIN table1 at t1 on t1 field1   m field INNER JOIN table2 at t2 on t2 field   t1 field WHERE t1 field3    SELECT MAX field3  FROM table1

User · Answer

yes you need to  use a having clause after the Group by clause    as the where is just to  filter the data on simple parameters    but group by followed by a Having statement is the idea to group the data and filter it on basis of some aggregate function

[sql] Sql Server : How to use an aggregate function like MAX in a WHERE clause

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to aggregate