Need help with MAX function - 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)

Need help with MAX function

Author

Topic

dtrance
Starting Member

10 Posts

Posted - 2004-11-15 : 11:11:30

SELECT * FROM dbo.TABLE1
WHERE color='green'

Lets say this query produces 3 results and I want the record with the highest value for column itemid. This is what I came up with. However it produces no results.

SELECT * FROM db.TABLE1
WHERE color='green' and itemid=(select max(itemid) from dbo.TABLE1)

What am I doing wrong?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-15 : 11:15:21

SELECT * FROM db.TABLE1
WHERE color='green' and itemid=(select max(itemid) from dbo.TABLE1 where color = 'green')

or maybe just
SELECT * FROM db.TABLE1
WHERE itemid=(select max(itemid) from dbo.TABLE1 where color = 'green')

SELECT top 1 * FROM db.TABLE1
WHERE color = 'green')
order by itemid desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dtrance
Starting Member

10 Posts

Posted - 2004-11-15 : 11:59:35

Thanks for the quick reply. Your solutions work great for the problem I presented. I was over simplifying my problem because I believed a simple MAX statement would fix my real one. Let me present my real case scenario.

I have a purchase orders table with line items stored in it. One line item will usually have 3 records. One to show when the item was put in, one to show when it was recieved, and one to show when it was invoiced. In my opinion this is poor design, but its a db of a commerical accounting app that was purchased eons ago and Im writing an asp front end while keeping the current application running.

Anyway, if I have an order with 2 line items, my query for all line items in the order table for this order would produce a total of 6 results. I need the two unique line items which have the highest A4GLIdentity column.

I used your suggestion here, but it only produces 1 line item.
SELECT * FROM dbo.POLINHST_SQL
WHERE ord_no='varord_no' and A4GLIdentity=(select max(A4GLIdentity)
FROM dbo.POLINHST_SQL
WHERE ord_no='varord_no')
ORDER BY line_no DESC

TIA for your help!

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-15 : 12:35:19

[code]SELECT *
FROM @POLINHST_SQL a
WHERE ord_no=@ord_no and exists
(
Select 1
FROM @POLINHST_SQL b
WHERE a.ord_no = b.ord_no
Group By b.ord_no, b.line_no
having a.A4GLIdentity = max(b.A4GLIdentity)
)
ORDER BY line_no DESC[/code]

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

dtrance
Starting Member

10 Posts

Posted - 2004-11-15 : 12:47:13

Thanks for the suggestion. I am getting more results than I should with the following. All line items x 3

SELECT *
FROM dbo.POLINHST_SQL a
WHERE ord_no='varord_no' and exists
(
select max(A4GLIdentity)
FROM dbo.POLINHST_SQL b
WHERE a.ord_no = b.ord_no and a.A4GLIdentity = b.A4GLIdentity
)
ORDER BY line_no DESC

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-15 : 12:52:28

I just edited my post

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

dtrance
Starting Member

10 Posts

Posted - 2004-11-15 : 12:58:37

You are the man! ;-)

Thanks a lot for taking the time to help me tuenty.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-15 : 13:21:14