MAX

Last Post 10 Nov 2011 01:12 AM by Eralper. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
JASBRYDEN
New Member
New Member

--
22 Sep 2011 07:56 AM
Hi

I am using the code below to get only the max date from the table but it does not seem to work

select itemno,location,oqoutstand,sqreceived,sqcanceled,sqrcpextra,unitcost from poporl
where dtcomplete in (select max(dtcomplete) from poporl
group by itemno)


The table has the date, item no, location and qty ordered, qty receive, qty cancel

I want the query to pull the newest date with all the information for each item, note that the item number may more than one in the table.

Thanks.
gunneyk
New Member
New Member

--
22 Sep 2011 09:44 AM
select itemno,location,oqoutstand,sqreceived,sqcanceled,sqrcpextra,unitcost
from poporl AS a
where a.dtcomplete = (select max(b.dtcomplete) from poporl AS b WHERE a.ItemNo = b.ItemNo)
JASBRYDEN
New Member
New Member

--
22 Sep 2011 11:21 AM
Thanks !!!

THat works perfect
Eralper
New Member
New Member

--
10 Nov 2011 01:12 AM
Hello JasBryden,
You can use the new T-SQL enhancements MAX() with OVER(Partition By...) clause

Here is a select query that might be usable

select 
    itemno,
    location,
    oqoutstand,
    sqreceived,
    sqcanceled,
    sqrcpextra,
    unitcost,
    dtcomplete_max = MAX(dtcomplete) OVER (PARTITION BY itemno)
from poporl
 


Here is a sample tutorial that can be referred : http://www.kodyaz.com/t-sql/sql-cou...lause.aspx
You are not authorized to post a reply.

Acceptable Use Policy