Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011

    Question Unanswered: Problems using DISTINCT?


    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):

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

  2. #2
    Join Date
    Nov 2004
    Provided Answers: 4
    Try this
    SELECT 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
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts