selecting max number

[jorbroni]

I have information in the following format:

1. XYZ Co. store 111 95,000.00
2. XYZ Co. store 122 100,000.00
3. XYZ Co. store 133 101,000.00

What I am trying to do is write a statement that will give me the max result which is 101,000.00. The problem is that I also need the company and store info as well.

This is the query I tried to write:

select distinct Company, Store, max(pmtamt)
from #tmp1 a, store_info b
where a.batchkey = b.batchkey
and b.batchno like 'rp1028'
group by company, store
order by company, store

When the results come back it will give me the same results.

Do anyone have any ideas?

 

[tlbroadbent]

Try this.
[tt]
Select Company, Store, Pmtamt
From #tmp1 a,
Join store_info b
On a.batchkey = b.batchkey
Where b.batchno = 'rp1028'
And PmtAmt=
(Select max(PmtAmt)
--I assume the pmtamt is in the store_info table.
--If this is incorrect change the following line.
From store_info
Where Company=b.Company)
order by company, store [/tt] If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm