General

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-18-2012
07:02 AM

06-18-2012
07:02 AM

Hi,

I want to know use of ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1), please explain in detail with example.

8 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-18-2012
10:12 AM

06-18-2012
10:12 AM

The use of the function is limited by the imagination of the developer. The row_number just does that numbers the row partitioned by col1, col2 order by col1. Did you try out this function on a table with smaller number of rows? Your best teacher is your own sql that you type and run and it's even better when you encounter errors doing so. Hope this helps! -Sankar

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-24-2012
10:13 PM

06-24-2012
10:13 PM

ROW_NUMBER is very similar to RANK function but for the following difference.

When the column on which RANK is performed contains identical values, the same RANK is provided to records with identical values. The next value (record) will be assigned a RANK reflecting the number number identical values before it.

Example:

RANK

Prod_id Units sold RANK

01 100 1

05 95 2

02 95 2

04 90 4 -> not 3

03 88 5

ROW_NUMBER does not bother about the identical values but numbers the rows with identical values as per the order mentioned by the ORDER BY.

Example:

ROW_NUMBER

Prod_id Units sold RANK

01 100 1

02 95 2

05 95 3

04 90 4

03 88 5

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-30-2013
05:21 PM

03-30-2013
05:21 PM

How do we use Row_Number if our rows returned ends up larger than the 'Integer' limit on it?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-21-2013
10:31 PM

09-21-2013
10:31 PM

why dont you cast it to decimal if the column exceeds the integer limit.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-22-2013
03:30 AM

09-22-2013
03:30 AM

Hi,

You can cast it to BIGINT, and if still the range is outside the limit, then cast it to Decimal(18,0).

Khurram

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-02-2015
02:28 AM

01-02-2015
02:28 AM

Hi ,

If row_number is use for just assigning the sequence , than what is the use of it ?

I mean where we actually have to use it .

Please give some real time example .

Thanks

SJ

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-28-2015
02:49 PM

01-28-2015
02:49 PM

SELECT a.col1

,CASE

WHEN b.cust_id IS NULL

THEN 0

ELSE 1

END AS is_true

FROM DB1.table1 AS a

LEFT JOIN DB2.table2 AS b

ON (

a.col3 = b.cust_id

AND a.start_date BETWEEN '2015-01-01' AND '2015-01-02'

) QUALIFY 1 = ROW_NUMBER() OVER (

PARTITION BY a.col1 ORDER BY b.col4

,b.col5

)

WHERE a.start_date BETWEEN '2015-01-01' AND '2015-01-02'

Is there any way to optimize the above query. Its taking so much CPU time

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-15-2017
05:32 AM

06-15-2017
05:32 AM

You can use a secondary index.

Plus you can try collecting stats;check the explain plan if there are other methods to optimise like decreasing number of joins etc.

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.