CODEWITHSUNDEEP
sql-serverselectdb2partitionrow-number
Rbell
Rbell

Reputation: 46

using Row_number() and Partition, then ordering desc, and selecting top result in DB2

i have a db2 linked server i'm running a query on through SQL Server.

select *
from openquery (DO,'
select distinct HOUSE_NUM, NAME, DOB, AGE, row_number()
    over(partition by DOB) rownum
from schema.INFO
where HOUSE_NUM = ''332''
group by HOUSE_NUM, NAME, DOB, AGE
order by NAME, rownum desc
limit 1
with ur');

the table has historical records, so there is a row for each age of the individual. i want to select the highest numbered row for each partition because that will give me their current age, however when i put limit 1 to select the top result i only get 1 row which ignores all the other people. the problem is there are multiple people living in a house and i need all of their ages, not just one of them. how do i select the top result of each partition in db2?

Before applying limit before applying limit

After applying limit, i need the other names too after applying limit, i need the other names too

Upvotes: 0

Views: 3665

Answers (1)

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

A Db2 query would look like this - rownumber cannot be referred to in the same query part this is why I used a CTE 

with temp as (
select distinct HOUSE_NUM, NAME, DOB, AGE
     , row_number() over(partition by HOUSE_NUM, NAME, DOB order by age desc) as rownum
  from schema.INFO
 where HOUSE_NUM = '332'
) 
select * 
  from temp 
 where rownum = 1

Hope this helps - due to the limited information about the data it is only a best guess

Upvotes: 1

Related Questions