Need some help with SQL

[Dim]

Need some help with SQL

In my tbl.. i save some document data.
A document always start with revision 1. if dokument is approwed it's locked
and forgotten
but if it aint, it will be remade and will have revision nr 2 alla other
data may be the same as rev. 1
some may even get remade/updated to rev 10-11... follow me so far?
so how do i select all rows with most current revision ????

mytable
ID docname author revision date
1 doc1 me 1 xxx-xx-xx
2 doc1 me 2 xxx-xx-xx
3 doc2 him 1 xxx-xx-xx
4 doc3 me 1 xxx-xx-xx
5 doc3 me 2 xxx-xx-xx
6 doc3 him 3 xxx-xx-xx
7 doc3 me 4 xxx-xx-xx
8 doc4 me 1 xxx-xx-xx
9 doc4 him 2 xxx-xx-xx

here i want some sql that select rows with most current revision and in this
example it would return 4 rows
id 2, id 3, id 7 and id 9


Thx in advance

 

[Allan Thompson]

Use a subselect as follows:

SELECT tblDoc.*
FROM tblDoc
where revision in
(select max(revision) from tblDoc);


--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184

 

[Dale Fye]

Allan,

That isn't going to work, because each docment has it's own max
revision number.

I think either of the following should work. Believe the first one
will be quicker.

SELECT D.*
FROM tblDoc D
INNER JOIN
(SELECT DocName, MAX(Revision) as MR
FROM tblDoc
GROUP BY DocName) as R
ON D.DocName = R.DocName
AND D.Revision = R.MR

OR

SELECT D.*
FROM tblDoc D
WHERE D.Revision = (SELECT MAX(Revision)
FROM tblDoc
WHERE DocName = D.DocName)

--
HTH

Dale Fye


Use a subselect as follows:

SELECT tblDoc.*
FROM tblDoc
where revision in
(select max(revision) from tblDoc);


--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184

 

[Allan Thompson]

Dale,

Good point. I looked at the data quickly and didn't read the problem
description carefully enough.

Allan

 

[Guest]

try this

select ID, docname, max(revision) from mytable
Group By ID, docname