Quick reply ... - 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

General SQL Server Forums

New to SQL Server Programming

Quick reply ...

Author

Topic

nssjari
Starting Member

46 Posts

Posted - 2005-11-14 : 02:08:52

For a query like below ...
How do i have to select only the latest revisions,
if i need to filter last current revisions of each document ...
where the revision could be either alphabetical or even numerical ...

Presently I get all revisions with the below query ...
Note: csd_revi is the field of CSD table for revisions.

---

select distinct
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi as "Document Reference No.",
csd1.csd_labl + ' ' as "Description"

from
E0437csd csd1,
E0437tra tra1,
E0437csd csd2,
E0437trd trd1,
E0437apr apr1,
E0437apc apc1,
E0437tra tra2,
E0437csd csd3,
E0437trd trd2

where
csd1.csd_orig = 'BS' and
csd1.csd_subj like '%WN1%' and
csd1.csd_type = 'D' and
tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra1.tra_part = 'PARSO' and
tra1.tra_type = 'A' and
csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi = tra1.tra_trno and
trd1.trd_trno = tra1.tra_trno and
trd1.trd_cc = '0' and
trd1.trd_part = 'PARSO' and
apr1.apr_docu = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
(apr1.apr_stat = apc1.apc_code or apr1.apr_stat = '') and
tra2.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra2.tra_part = csd1.csd_ownr and
csd3.csd_orig + csd3.csd_subj + csd3.csd_type + csd3.csd_numb + csd3.csd_revi = tra2.tra_trno and
trd2.trd_trno = tra2.tra_trno

order by
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi

---

Life is beautiful ... When you smile ...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-14 : 02:23:55

It would be great if you can post some sample data .. ???

Complicated things can be done by simple thinking

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-14 : 02:35:01

is this what you want ?

select csd1.csd_orig, csd1.csd_subj, csd1.csd_type, csd1.csd_numb, max(csd1.csd_revi) as "Document Reference No.", csd1.csd_labl, as "Description"
from <your table list>
where <your where condition>
group by csd1.csd_orig, csd1.csd_subj, csd1.csd_type, csd1.csd_numb, csd1.csd_labl

[KH]

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-11-14 : 03:10:14

If you follow this link it tells you what you need to provide so that someone can help you

[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources