CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

Select top n rows based on multiple criteria

Forgive me for what's probably an awful title; I struggled to explain what I'm trying to do in one line of text.

SQL Server 2005

I have a table called ItemDataSheets that looks like this:

Id   ItemId    FilePath                FileDescription    FileType
------------------------------------------------------------------
1    JOD141    /files/JOD141_a.pdf     DataSheet ABC      1
2    JOD141    /files/JOD141_b.pdf     DataSheet LMN      1
3    JOD141    /files/JOD141_c.pdf     DataSheet XYZ      2
4    JOD141    /files/JOD141_d.pdf     DataSheet POI      3
5    JOD141    /files/JOD141_e.pdf     DataSheet QWE      3

For a given ItemId, I need to return 1 row for each FileType it has. If it has more than 1 of a certain file type, then I need the row with the highest Id for that FileType.

So for ItemId JOD141, I'd want back:

Id   ItemId    FilePath                FileDescription    FileType
------------------------------------------------------------------
2    JOD141    /files/JOD141_b.pdf     DataSheet LMN      1
3    JOD141    /files/JOD141_c.pdf     DataSheet XYZ      2
5    JOD141    /files/JOD141_e.pdf     DataSheet QWE      3

Upvotes: 1

Views: 3028

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

SELECT *
FROM
  ItemDataSheets  i
  INNER JOIN (SEELECT max(id) id, 
                      fileType 
               from ItemDataSheets 
               GROUP BY 
                    fileType ) maxdID
   on i.filetype = maxid.filetype
       and i.id = mqaxid.id

or

SELECT
     *    
FROM (SELECT 
          *,
          ROW_NUMBER() OVER(partition BY fileType ORDER BY id DESC) AS RankValue
          FROM ItemDataSheets 
     ) i
WHERE RankValue=1

Upvotes: 8

Related Questions