max() in the where? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

max() in the where?

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-07-18 : 15:36:23

Hi, All!

I need your help to get this max function to work in the where clause. Basically I am after a mailing list for certain customers. The account table has a AcctName column, but for some of them, they are null. For these accounts, I need to bring in the OrderContact from Orders table, but need to get the name for the latest order.

Here are the tables and data.
CREATE TABLE [dbo].[Accts] (
[Acct] [int] NULL ,
[AcctName] [nvarchar] (50) NULL ,
[AcctContact] [nvarchar] (50) NULL ,
[Address] [nvarchar] (50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] NULL ,
[Acct] [int] NULL ,
[OrderDate] [smalldatetime] NULL ,
[OrderContact] [nvarchar] (50) NULL
) ON [PRIMARY]

insert into accts values(1,'ABC', 'Jeff', '123 Main')
insert into accts values(2,'Zipz',NULL,'3221 Wall')
insert into Orders values(1,1, '2005-01-01 ','Whos who')
insert into Orders values(2,2, '2005-01-01 ', 'Well')
insert into Orders values(3,2,'2006-02-01 ','Got it')

And here's what I got.
select AcctName, Address, AcctContact, OrderContact
from Accts join Orders on Accts.Acct=Orders.Acct
where OrderDate = (select max(OrderDate) from Orders group by Acct)

TIA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-18 : 15:39:56

What is the expected result set given this sample data?

Tara Kizer

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-07-18 : 15:54:14

Here they are:

1 ABC Jeff 123 Main
2 Zipz Got it 3221 Wall

It is the "Got it" part. It has to come from Orders and it has to be based on the max OrderDate, i.e. it should be "Got it", but not "Well".

Thanks!

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-18 : 15:57:49

Try putting it in a having clause.

select AcctName, Address, AcctContact, OrderContact
from Accts join Orders on Accts.Acct=Orders.Acct
group by AcctName, Address, AcctContact, OrderContact
having OrderDate = max(OrderDate)

union all

select '','','',ordercontact
from orders a
where not exists ( select OrderContact
from Accts join Orders b on Accts.Acct=Orders.Acct
where a.ordercontact = b.ordercontact
group by OrderContact
having OrderDate = max(OrderDate))
group by ordercontact
having orderdate = max(orderdate)

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2006-07-18 : 16:02:26

select A.Acct,A.AcctName, A.Address, A.AcctContact, O.OrderContact,O.OrderDate
from (Accts A join Orders O on A.Acct=O.Acct )
Where O.OrderDate In (Select max(C.OrderDate) from Orders C Where A.Acct = C.Acct group by C.Acct)

1 ABC 123 Main Jeff Whos who 2005-01-01 00:00:00
2 Zipz 3221 Wall NULL Got it 2006-02-01 00:00:00

Choose which clumns U want.

With Regards
BSR

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-07-18 : 16:02:49

Yah, that is where I am having trouble with, because the message says
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

I have rearranged the query many different ways, but have not figured it out.

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-18 : 16:07:47

I didn't notice you already had Orders in your query you don't even need a subquery.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-18 : 16:07:47

Here is my solution. I'm sure someone else will be able to figure this out without a union. The first query in the union is for the ones that do not have nulls. The second is for the ones with nulls.


select a.Acct, a.AcctName, a.AcctContact, a.Address
from accts a
inner join orders o
on a.acct = o.acct
where a.acctcontact is not null
union all
select a.Acct, a.AcctName, o.OrderContact, a.Address
from accts a
inner join orders o
on a.acct = o.acct
inner join
(
	select o2.acct, max(o2.orderdate) as orderdate
	from orders o2
	inner join accts a2
	on a2.Acct = o2.Acct
	where a2.acctcontact is null
	group by o2.acct
) t
on o.acct = t.acct and o.orderdate = t.orderdate

Tara Kizer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-18 : 16:16:21

Here's one without the union. It handles both null and not null.


select 
	a.Acct, 
	a.AcctName, 
	Contact = 
		CASE 
			WHEN a.AcctContact IS NULL THEN o.ordercontact 
			ELSE a.AcctContact 
		END, 
	a.Address
from accts a
inner join orders o
on a.acct = o.acct
inner join
(
	select o2.acct, max(o2.orderdate) as orderdate
	from orders o2
	inner join accts a2
	on a2.Acct = o2.Acct
	group by o2.acct
) t
on o.acct = t.acct and o.orderdate = t.orderdate

Tara Kizer

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-07-18 : 16:17:03

Great! It works, but it is way complicated than I thought.
When I throw this with my other half dozen requirements/solutions, I don't think I can follow through the maze.

Let me try.

Thanks a lot!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-18 : 16:45:23

quote:
Originally posted by humanpuck

Try putting it in a having clause.

select AcctName, Address, AcctContact, OrderContact
from Accts join Orders on Accts.Acct=Orders.Acct
group by AcctName, Address, AcctContact, OrderContact
having OrderDate = max(OrderDate)

union all

select '','','',ordercontact
from orders a
where not exists ( select OrderContact
from Accts join Orders b on Accts.Acct=Orders.Acct
where a.ordercontact = b.ordercontact
group by OrderContact
having OrderDate = max(OrderDate))
group by ordercontact
having orderdate = max(orderdate)

I can't get this solution to work with the DDL provided by Hommer.

Tara Kizer

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-18 : 16:52:42

sorry bout that try this

select AcctName, Address,
case when AcctContact is null
then OrderContact
else acctcontact
end as Contact
from Accts inner join Orders on Accts.Acct=Orders.Acct
group by AcctName, Address, AcctContact, OrderContact
having max(OrderDate) = (select max(orderdate) from orders)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-18 : 16:56:08

quote:
Originally posted by humanpuck

sorry bout that try this

select AcctName, Address, AcctContact, OrderContact
from Accts inner join Orders on Accts.Acct=Orders.Acct
group by AcctName, Address, AcctContact, OrderContact
having max(OrderDate) = (select max(orderdate) from orders)

It now no longer blows up with a syntax error, but it also doesn't provide the expected result set Hommer posted. Check this out:


CREATE TABLE [dbo].[Accts] (
[Acct] [int] NULL ,
[AcctName] [nvarchar] (50) NULL ,
[AcctContact] [nvarchar] (50) NULL ,
[Address] [nvarchar] (50) NULL 
) ON [PRIMARY]
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] NULL ,
[Acct] [int] NULL ,
[OrderDate] [smalldatetime] NULL ,
[OrderContact] [nvarchar] (50) NULL 
) ON [PRIMARY]

insert into accts values(1,'ABC', 'Jeff', '123 Main')
insert into accts values(2,'Zipz',NULL,'3221 Wall')
insert into Orders values(1,1, '2005-01-01 ','Whos who')
insert into Orders values(2,2, '2005-01-01 ', 'Well')
insert into Orders values(3,2,'2006-02-01 ','Got it')


select AcctName, Address, AcctContact, OrderContact
from Accts join Orders on Accts.Acct=Orders.Acct
group by AcctName, Address, AcctContact, OrderContact
having max(OrderDate) = (select max(orderdate) from orders)

drop table accts, orders

Here's his expected result set:
1 ABC Jeff 123 Main
2 Zipz Got it 3221 Wall

Tara Kizer

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-18 : 17:00:21

select AcctName, Address,
case when AcctContact is null
then OrderContact
else acctcontact
end as Contact
from Accts inner join Orders on Accts.Acct=Orders.Acct
group by AcctName, Address, AcctContact, OrderContact
having max(OrderDate) = (select max(orderdate) from orders)

union all

select acctname, address, acctcontact
from Accts inner join Orders on Accts.Acct=Orders.Acct
where acctcontact is not null

this works I didn't notice the results that were wanted

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-18 : 17:02:25

Welcome to the club humanpuck!

Hommer, now you'll need to compare the second solution that I posted to the other solutions here to see which has the better execution plan.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources