The Last Order of all the product

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

General SQL Server Forums

New to SQL Server Programming

The Last Order of all the product

Author

Topic

Flaterik
Starting Member

26 Posts

Posted - 2012-05-21 : 09:26:51

Hi people i have the table DORIG in my db.
In this table i have all the row of the invoice.

The table have this structure

id_Dorig, Cd_Cf, datadoc, cd_ar and more other field that are not usefull.

What i whant is data of the last document for all the article.

The data that i want extract is

Cd_ar , datadoc , cd_cf

If i use select MAX(datadoc) , cd_ar
from dorig group by cd_ar

I have the cd_ar and the data but i can't extract the cd_cf. If i insert it in the query, duplicate result.

Thank you

************************************************
the world is strange but people are crazy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 09:35:32

[code]
select *
from
(
select *, rn = row_number() over (partition by cd_ar order by datadoc desc)
from dorig
) d
where d.rn = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Flaterik
Starting Member

26 Posts

Posted - 2012-05-21 : 09:40:24

God bless you!

************************************************
the world is strange but people are crazy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 09:43:46

welcome

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 16:19:24

quote:
Originally posted by Flaterik

Hi people i have the table DORIG in my db.
In this table i have all the row of the invoice.

The table have this structure

id_Dorig, Cd_Cf, datadoc, cd_ar and more other field that are not usefull.

What i whant is data of the last document for all the article.

The data that i want extract is

Cd_ar , datadoc , cd_cf

If i use select MAX(datadoc) , cd_ar
from dorig group by cd_ar

I have the cd_ar and the data but i can't extract the cd_cf. If i insert it in the query, duplicate result.

Thank you

************************************************
the world is strange but people are crazy

you were close. to just get other data you just needed to modify it as


SELECT t.*
FROM dorig t
INNER JOIN (select MAX(datadoc) AS MaxDate, cd_ar 
from dorig group by cd_ar
)t1
ON t1.cd_ar = t.cd_ar
AND t1.MaxDate = t.datadoc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources