Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 - row_number function - Need 1st partition row in output

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 - row_number function - Need 1st partition row in output
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

Senior Member


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

PostPosted: Wed Sep 09, 2020 6:43 pm    Post subject:
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: 820
Location: Maryland

PostPosted: Wed Sep 09, 2020 9:47 pm    Post subject: Re: DB2 - row_number function - Need 1st partition row in output
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    Post subject:
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: 820
Location: Maryland

PostPosted: Thu Sep 10, 2020 8:08 pm    Post subject:
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

Senior Member


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

PostPosted: Fri Sep 11, 2020 3:45 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to Assign a Function key to Mainf... upendrasri TSO/ISPF 4 Sat May 30, 2020 9:03 pm
No new posts Write 9(07) comp-3 data into output file clearskynot DFSORT/ICETOOL 4 Thu Apr 16, 2020 11:00 pm
No new posts DFSORT Output file order query A_programmers DFSORT/ICETOOL 2 Thu Mar 26, 2020 11:59 pm
No new posts Rexx STRIP Equivalent function in CLIST upendrasri CLIST & REXX 5 Mon Mar 16, 2020 9:57 pm
No new posts OUTTRAP Equivalent function in CLIST upendrasri CLIST & REXX 5 Thu Mar 12, 2020 12:12 pm

Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us