-1

I have data for different institutions with different currencies, dates and corresponding values. Need a query to get column of 'Required values' which correspond to max date grouped by each institution and currency

Institution Name    Currency    Date    Values      Required Values

Institution 1             CAD           date1    100                     100
Institution 1             USD           date2    200                 200
Institution 2             CAD           date3    150                 250
Institution 2             CAD           date4    300                 250
Institution 2             CAD           date5    250                 250
Institution 2             USD           date6    300                 300
Institution 3             CAD           date7    400                 400
Institution 3             USD           date8    50              100
Institution 3             USD           date9    75              100
Institution 3             USD           date10   100                 100
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
M.A.
  • 11
  • 1
  • What have you tried? What errors are you getting? Show some effort.... – S3S May 23 '17 at 21:17
  • I tried inner joining the table with max date. I am getting the correct result but query is too long joining on different columns as I put only a portion of the table but there are also other columns. I was just looking for a better way of writing the query. If you cannot help that's fine because I have a working query – M.A. May 23 '17 at 21:56
  • Fwiw, I think this question has been answered in another StackOverflow question https://stackoverflow.com/questions/755918/simple-query-to-grab-max-value-for-each-id. At the very least, the accepted answer mentions a common pattern for the problem you're having in queries -- the "groupwise maximum", or max value BY some group (of values). – RoboBear May 25 '17 at 00:40

1 Answers1

0

Use row_number():

select t.*
from (select t.*,
             row_number() over (partition by InstitutionName, Currency
                                order by date desc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon. This query gets me only values corresponding to seqnum=1. I want to have the column to have all the seqnums and the required values column to have values corresponding to seqnum =1 when seqnum <> 1 – M.A. May 23 '17 at 22:10
  • @M.A. . . . You should ask *another* question. Provide sample data and desired results. Your interpretation is definitely not what I think the question is asking. – Gordon Linoff May 24 '17 at 02:46