IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

DB2 - row_number function - Need 1st partition row in output


 
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: :: View next topic  
Author Message
Q5P418

New User


Joined: 10 May 2020
Posts: 7
Location: USA

PostPosted: Wed Sep 09, 2020 8:35 am
Reply with quote

Problem: SQL doesn't give output having first partition row.

I tried below SQL but it wont work.

Books Table:
book_id
rating
title
isbn
publisher_id


Code:
WITH cte AS (
    SELECT
        publisher_id,
        ROW_NUMBER() OVER (
            PARTITION BY publisher_id
            ORDER BY rating DESC
        ) row_num,
        book_id,
        rating,
        title
    FROM
        books
    WHERE
        publisher_id IS NOT NULL
)
SELECT
  *
FROM
    cte
WHERE
    row_num >= 1 AND
    row_num >= 2;
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2503
Location: NY,USA

PostPosted: Wed Sep 09, 2020 6:43 pm
Reply with quote

why do you have row_num >= 2; then? Infact not sure why you coded row_num in WHERE.
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 876
Location: Maryland

PostPosted: Wed Sep 09, 2020 9:47 pm
Reply with quote

Q5P418 wrote:
I tried below SQL but it wont work.

Please explain this “wont work”?
1) you cannot type this SQL as it is?
2) this SQL is not accepted by your “computer”?
3) you get syntax error message?
4) you get execution error message?
5) you do not get any output, or SQL enters an indefinite loop, or some other missing results?
6) you get some unexpected results?
7) you get a mixture of correct, and incorrect results?
8) you get correct results for some input data, but incorrect ones for others?
9) while your SQL is running, your computer got fire, and exploded?
10) something else?
Back to top
View user's profile Send private message
Q5P418

New User


Joined: 10 May 2020
Posts: 7
Location: USA

PostPosted: Thu Sep 10, 2020 9:03 am
Reply with quote

Rohit Umarjikar/sergeyken,

I need output for publisher_id records which have at least 2 partitions.

I tried row_num >= 1 but it gives publisher_id records which have only 1 partition. That does meet my requirement.
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 876
Location: Maryland

PostPosted: Thu Sep 10, 2020 8:08 pm
Reply with quote

Q5P418 wrote:
Rohit Umarjikar/sergeyken,

I need output for publisher_id records which have at least 2 partitions.

I tried row_num >= 1 but it gives publisher_id records which have only 1 partition. That does meet my requirement.


Definitely, row_num >= 1 is equivalent to “allow ANY possible value”; that’s why you get all possible rows in your results.

The clause ORDER BY rating DESC when used inside of OVER (PARTITION ...) does not give any effect at all; you may need to order the results of your outer SELECT, if really needed.

In general, the overall logic of SQL is wrong. I don’t have access to DB2 right now to test required changes.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2503
Location: NY,USA

PostPosted: Fri Sep 11, 2020 3:45 am
Reply with quote

Q5P418 wrote:
Rohit Umarjikar/sergeyken,

I need output for publisher_id records which have at least 2 partitions.

I tried row_num >= 1 but it gives publisher_id records which have only 1 partition. That does meet my requirement.


Isn’t it a simple group by having count(*) > 1 ? Pls Show us the sample data and expected output and you may get different ways to do the same thing.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> DB2
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts Altering output file attributes All Other Mainframe Topics 5
No new posts How to Assign a Function key to Mainf... TSO/ISPF 4
No new posts Write 9(07) comp-3 data into output file DFSORT/ICETOOL 4
No new posts DFSORT Output file order query DFSORT/ICETOOL 2
No new posts Rexx STRIP Equivalent function in CLIST CLIST & REXX 5

Back to Top