CODEWITHSUNDEEP
sqlteradata
Cynthia Mar
Cynthia Mar

Reputation: 21

Using "Rows between 1 Following and 1 Following in Teradata" to Select the next row's values

I am trying to select the next row's value into a another column. However, I get both the current and next value..

The SQL query being used is the following:

SELECT DISTINCT
  "Title",
  "US Release Date Title",
  MAX("Title") OVER (
    PARTITION BY "Brand"
    ORDER BY "US Release Date Title" DESC, "Title" DESC
    ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
) AS "Predecessor"

And a snippet the output from that query is this:

Call of Duty: Black Ops IIII    |  2018-10-12  |  Call of Duty: Black Ops IIII    |  2018-10-12
Call of Duty: Black Ops IIII    |  2018-10-12  |  Call of Duty: WWII              |  2017-11-03
Call of Duty: WWII              |  2017-11-03  |  Call of Duty: Infinite Warfare  |  2016-11-04
Call of Duty: WWII              |  2017-11-03  |  Call of Duty: WWII              |  2017-11-03
Call of Duty: Infinite Warfare  |  2016-11-04  |  Call of Duty: Advanced Warfare  |  2014-11-03
Call of Duty: Infinite Warfare  |  2016-11-04  |  Call of Duty: Infinite Warfare  |  2016-11-04
Call of Duty: Advanced Warfare  |  2014-11-03  |  null                            |  null
Call of Duty: Advanced Warfare  |  2014-11-03  |  Call of Duty: Advanced Warfare  |  2014-11-03

My desired output is this: And a snippet the output from that query is this:

Call of Duty: Black Ops IIII    |  2018-10-12  |  Call of Duty: WWII              |  2017-11-03
Call of Duty: WWII              |  2017-11-03  |  Call of Duty: Infinite Warfare  |  2016-11-04
Call of Duty: Infinite Warfare  |  2016-11-04  |  Call of Duty: Advanced Warfare  |  2014-11-03
Call of Duty: Advanced Warfare  |  2014-11-03  |  null                            |  null

Upvotes: 2

Views: 5366

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you need the select distinct before the window function. Or use aggregation:

SELECT "Title", "US Release Date Title",
       MAX("Title") OVER (PARTITION BY MAX("Brand")
                          ORDER BY "US Release Date Title" DESC,
                                   "Title" DESC
                          ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
                          ) AS "Predecessor"
FROM t
GROUP BY "Title", "US Release Date Title";

You do need an aggregation function around Brand for this to work.

Upvotes: 1

Related Questions