Max Count - 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

Max Count

Author

Topic

cosmarchy
Starting Member

14 Posts

Posted - 2008-09-04 : 15:24:18

Hello

Could someone please help me with the following problem.

I have an Access table layed out like this:


FileRevision	FileDescription		Code
1		Machined Parts		AB1701
1		Bought Parts		AB1701
1		Machined Parts		AB1801
1		Bought Parts		AB1801
1		Test Routine		AB1801
2		Machined Parts		AB1701
2		Bought Parts		AB1701
2		Machined Parts		AB1801
2		Bought Parts		AB1801
2		Test Routine		AB1801
3		Machined Parts		AB1801
3		Bought Parts		AB1801

What I need to do is get a query that I which I can enter the code (AB1701 etc) and it returns all the files at the latest revision.

For instance
If I enter AB1801, the query would return this:


3		Machined Parts		AB1801
3		Bought Parts		AB1801

as these are the latest revision for that code.

As another thought, how would I achieve the same result if I was to change the FileRevision to A,B,C etc?

Thanks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-09-04 : 15:27:43

You could do this by using row_number() if you are using Sql Server 2005:

select  *
from    ( select    row_number() over ( partition by Code order by FileRevision desc )
                    as rn, *
          from      YourTable
        ) a
where   rn = 1
and Code = @Code

Or:

select  t1.*
from    YourTable t1
join    ( select    Code,
                    max(FileRevision) as MaxFileRevision
          from      YourTable
          group by  Code
        ) t2 on t1.Code = t2.Code
                and t1.FileRevision = t2.MaxFileRevision
where t1.Code = @Code

cosmarchy
Starting Member

14 Posts

Posted - 2008-09-04 : 16:27:39

Unfortunately I do not have the luxury of SQL server :(

I am constrained to MS Access!!

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-05 : 00:59:49

quote:
Originally posted by cosmarchy

Unfortunately I do not have the luxury of SQL server :(

I am constrained to MS Access!!

Thanks

post this in access forum then

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-05 : 03:01:13

select t1.*
from YourTable t1
where t1.code='AB1801' and
FileRevision=(select max(FileRevision) as FileRevision from yourTable where code=t1.code)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-05 : 03:29:36

This is not an Access solution.

SELECT TOP 1 * WITH TIES
FROM Table1
WHERE Code = @Code
ORDER BY FileRevision DESC

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources