|
View previous topic :: View next topic
|
| Author |
Message |
Q5P418
New User
Joined: 10 May 2020
Posts: 7
Location: USA
|
|
|
|
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 |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| why do you have row_num >= 2; then? Infact not sure why you coded row_num in WHERE. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2022
Location: USA
|
|
|
|
| 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 |
|
|
Q5P418
New User
Joined: 10 May 2020
Posts: 7
Location: USA
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2022
Location: USA
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| 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 |
|
|
|
|
