grouping unique name in table

[chai]

Hi!

i have a table with 3 fields: ParentFolder,DocumentNo and VersionNo
the DocumentNo give each document a unique number. ParentFolder is a number specifying which folder a document is in. and each document(with same name) can have many VersionNo(1,2,3 etc).

i am creating a house keeping function for user. this function accepts a number from user specifying how many version he would like to keep.

i have created a page that list out all folder in the db with a checkbox. user is able to checked the folder they want to housekeep.

the problem is how do i write a sql that will 'group' documents with same name, delete the document base on the input.

since the system only get the folder id and not each document id individually the system won't be able to pinpoint each document.

in dire need of help
Thanks in advance

[jwshepherd]

you will either need to add a second table to keep track of document names and Document numbers relations or add a DocumentName Column to your table.

[pclfw]

Sounds like work for a stored procedure.
The problem being that the easiest way to do this is to have a sub-query select the documents grouped on the parentfolder and documentnumber, and use a 'TOP X' qualifier. Then have the main select query use the output of the subquery with the NOT IN comparison.