Problems using DISTINCT?

conork · #1 (**permalink**) 12-09-11, 06:35

Hi,

I am still finding my feet with sql server but I am having a problem trying to solve a query issue.

I want to allow users to upload different versions of documents. However I want them to be able to access the previous versions if they need to.
Below, is some info (there are other fields, but not necessary here) from a table 'file_resources':

file_id file_name version original_file_id
406 ppt1 4 403
405 ppt3 3 403
404 ppt1 2 403
403 ppt1 1 403
402 excel1 1 402

The problem I have is that I want to display the latest version details by default but have a link to previous versions of the document.
So, when I run the following sql:

SELECT file_id, version
FROM file_resources
ORDER BY file_id DESC, version DESC

it returns:

file_id version
406 4
405 3
404 2
403 1
402 1

But what I need to get is the 2 unique documents (The latest file_id where original_file_id is duplicated):
406
402

I have tried using DISTINCT but it only returns 1 record and not 2.
Any help would be appreciated,
Thanks
Conor

Wim · #2 (**permalink**) 12-09-11, 08:02

Try this

Code:

SELECT file_id,
	file_name,
	version,
	original_file_id
FROM MyTable
	INNER JOIN (SELECT file_name, MAX(Version) as MaxVersion
			FROM MyTable as MT
			GROUP BY file_name
			) as T ON
		MyTable.file_name = T.file_name AND
		MyTable.Version = T.MaxVersion

Internet Services

Web Development

Digital Marketing

Consumer Tech