CODEWITHSUNDEEP
sqlsql-serverselectdistinct
Sergej
Sergej

Reputation: 13

MS SQL 2008 Generate distinct ID on combination of two columns

I'm having trouble in creating a query that would return newest rows based on unique combination of 2 columns(Material and Analysis) while older(by date) combinations are dismissed.

I have already filtered needed columns out:

ID   Col1   Col2    Col3    MaterialID  Material    AnalysisID      FKS
126  E006   E5       e3        44         PPL           39        Density
126  E006   E5       e3        44         PPL           107       Presure
126  E006   E5       e3        44         PPL           95        Amount of C
126  E006   E5       e2        44         PPL           95        Amount of C

So the forth row should be dismissed because we have newer Material analysis of PPL for Amount of C.

My current query looks like this:

   SELECT TOP 100
      a.[ID]
      ,a.[Col1]
      ,a.[Col1]
      ,a.[Col1]
      ,b.[MaterialID]
      ,b.[Material]
      ,b.[AnalysisID]   
   FROM [Table1] a

  INNER JOIN
  [Table2] b
ON
a.[ID]=b.[MaterialID]

WHERE a.[Col1] like '%E%'
ORDER BY
  b.[Date] DESC

Thanks for any contribution. If I have not make it clear I'll try to clarify.

BR

Upvotes: 1

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This calls for row_number():

SELECT *
FROM (SELECT . . .  ,
             ROW_NUMBER() OVER (PARTITION BY Material, AnalysisID
                                ORDER BY date DESC
                               ) as seqnum
      FROM [Table1] a INNER JOIN
           [Table2] b
           ON a.[ID] = b.[MaterialID]
      WHERE a.[Col1] like '%E%'
     ) ab
WHERE seqnum = 1;

Upvotes: 1

Related Questions