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.
| Author |
Topic |
|
Anushka
Yak Posting Veteran
79 Posts |
 Posted - 2008-04-22 : 07:49:31
|
| ID ITEMID DATE QTY1 XYZ 1/1/2008 12 XYZ 2/4/2008 33 XYZ 34/2008 24 ABC 1/3/2008 4 5 ABC 1/4/2008 16 CAB 12/4/2008 3Actually i neeed max(date) record....as the id is uniqueif i will do group by itemid it is not possible...as id is unique (again iam getting all values)[i wrote select max(date),itemid,date,qty from table group by id,itemid,qty-this is 100% wrong].....O/P:This is the o/p i requireID ITEMID DATE QTY3 XYZ 34/2008 2 5 ABC 1/4/2008 16 CAB 12/4/2008 3so,how it is possible.........[is it trough courser's it possible,if so how]Hi,Thanx for reply for my previous post....iam getting o/p if i retrive from single table ...but the fields are from different tables i have to select for example i wrote:select p.receiveddate AS [Last Received Date],P.PURCHASENUMBER AS [Ref Doc#],p.itemid AS [Item ID],P.DESCRIPTION AS [Description],I.QtyOnHand [Stock As On Date],P.ITEMUNITPRICE as [Rate],P.SUBTOTAL as [Value],P.ReceivedQty as [Received Qty]FROM(SELECT ROW_NUMBER() OVER( PARTITION BY p.ITEMID Order BY p.receiveddate DESC) AS RowNo,receiveddate,PURCHASENUMBER,ITEMID,DESCRIPTION,QtyOnHand,ITEMUNITPRICE,SUBTOTAL,ReceivedQtyFROM pURCHASEDETAIL P,inventorybywarehouse i)tWHERE t.RowNo=1 ORDER BY p.PURCHASENUMBER |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
thanx visakh16 n raky but.............