How to display a columns current row and next row value in single select query?

Question

Post reply

How to display a columns current row and next row value in single select query?

nareshmca

SSC Rookie

Points: 28
More actions
January 20, 2010 at 7:57 pm

#215823

Hi All,
I have a requirement to obtain the value in following format.
Table Looks Like...
sol_sub_cat_Id sol_cat_id sol_sub_cat_item sol_sub_cat_sequence
-------------- ---------- ---------------- --------------------
1 2 Features 1
2 2 Programs 2
3 2 Users/Where 3
4 2 Advantages 4
5 2 Public Access 5
19 2 Security 6
20 2 Demo 7
8 3 Features 1
9 3 Programs 2
10 3 Users/Where 3
11 3 Advantages 4
12 3 Public Access 5
30 3 Security 6
35 3 Demo 7
Then The Output should be like this...
sol_sub_cat_Id sol_cat_id sol_sub_cat_item sol_sub_cat_sequence Next_sequence
-------------- ---------- ---------------- -------------------- -------------
1 2 Features 1 2
2 2 Programs 2 3
3 2 Users/Where 3 4
4 2 Advantages 4 5
5 2 Public Access 5 19
19 2 Security 6 20
20 2 Demo 7 1
8 3 Features 1 9
9 3 Programs 2 10
10 3 Users/Where 3 11
11 3 Advantages 4 12
12 3 Public Access 5 30
30 3 Security 6 35
35 3 Demo 7 8
thanks in advance!
Thanks for your reply. Sorry for the confusion.
First i have to tell one thing, sol_sub_cat_Id is a PK column, sol_cat_id is a FK column. I am looking for an output based on FK Column(sol_cat_id). What I mean is...
Table Data Looks Like...
sol_sub_cat_Id sol_cat_id sol_sub_cat_item sol_sub_cat_sequence
-------------- ---------- ---------------- --------------------
1 2 Features 1
2 2 Programs 2
3 2 Users/Where 3
4 2 Advantages 4
5 2 Public Access 5
6 2 Security 6
7 2 Demo 7
8 3 Features 1
9 3 Programs 2
10 3 Users/Where 3
11 3 Advantages 4
12 3 Public Access 5
13 3 Security 6
14 3 Demo 7
Output Data should be...
sol_sub_cat_Id sol_cat_id sol_sub_cat_item sol_sub_cat_sequence Next_sequence
-------------- ---------- ---------------- -------------------- -------------
1 2 Features 1 2
2 2 Programs 2 3
3 2 Users/Where 3 4
4 2 Advantages 4 5
5 2 Public Access 5 6
6 2 Security 6 7
7 2 Demo 7 1
8 3 Features 1 9
9 3 Programs 2 10
10 3 Users/Where 3 11
11 3 Advantages 4 12
12 3 Public Access 5 13
13 3 Security 6 14
14 3 Demo 7 8
Please carefully check the output result. The last column (Next_sequence) in the output is the value of the sol_sub_cat_Id column in next row of sol_cat_id column group(2 or 3). Once the sol_sub_cat_Id ends for that group(2 or 3), then it should use the starting value sol_sub_cat_Id of that group(2 or 3).
For group 2:- the last row is "7 2 Demo 7 1"; here the last column value 1 should be the sol_sub_cat_Id of first row(1 2 Features 1) of group 2.
For group 3:- the last row is "14 3 Demo 7 8"; here the last column value 1 should be the sol_sub_cat_Id of first row(8 3 Features 1) of group 3.
I hope you got the issue.
thanks!

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 1

You can do that by joining the same table twice using ur primary key ( sequence number )

Select current.*,next_seq.*

From tableA current, tableA next_seq

where current.sol_sub_cat_Id+1 = next_seq.sol_sub_cat_Id

Regards,
Raj

http://Strictlysql.blogspot.com

nareshmca SSC Rookie Points: 28 More actions · Answer 2

Hi Thanks for your quick reply, one way this is working fine. But i have an issue here...

sol_sub_cat_Id sol_cat_id sol_sub_cat_item sol_sub_cat_sequence

-------------- ---------- ---------------- --------------------

1 2 Features 1

2 2 Programs 2

3 2 Users/Where 3

4 2 Advantages 4

5 2 Public Access 5

19 2 Security 6

20 2 Demo 7

8 3 Features 1

9 3 Programs 2

10 3 Users/Where 3

11 3 Advantages 4

12 3 Public Access 5

30 3 Security 6

35 3 Demo 7

Then The Output should be like this...

the Output should looks like...

sol_sub_cat_Id sol_cat_id sol_sub_cat_item sol_sub_cat_sequence Next_sequence

-------------- ---------- ---------------- -------------------- -------------

1 2 Features 1 2

2 2 Programs 2 3

3 2 Users/Where 3 4

4 2 Advantages 4 5

5 2 Public Access 5 19

19 2 Security 6 20

20 2 Demo 7 1

8 3 Features 1 9

9 3 Programs 2 10

10 3 Users/Where 3 11

11 3 Advantages 4 12

12 3 Public Access 5 30

30 3 Security 6 35

35 3 Demo 7 8

Can you please help me on this...

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 3

If u have gaps then u can try this

Select current_seq.*,next_seq.* from

(

Select *,row_number() Over (order by id asc ) as seq

from tablea ) as current_seq, ( Select *,row_number() Over (order by id asc ) as seq

from tablea ) next_seq

where current_seq.seq + 1 = next_seq.seq

where id isur sequence number or primary key

Regards,
Raj

http://Strictlysql.blogspot.com

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 4

If u want it in the order in which its stored in the database then its not possible. You may need to create a column with auto increment. Database can decide to store any row in any order and its non determinitstic unless you have clustered index on the table.

Even with a clustered index a select * from table doesnt guarentee to provide the results in the clustered index order.

So creating a columns say seq with auto increment can be helpful.

Regards,
Raj

http://Strictlysql.blogspot.com

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

It looks like the data already has an "auto-increment" column but just has a couple of gaps. Use ROW_NUMBER() to establish a "gapless" self join and go from there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 6

@jeff,

His sample result has 19,20 and after that 8,9. So I guessed he felt he needs it in the order in which Database stores ( or unordered scan resultorder ).

Regards,
Raj

http://Strictlysql.blogspot.com

nareshmca SSC Rookie Points: 28 More actions · Answer 7

Thanks for your reply. Sorry for the confusion.

First i have to tell one thing, sol_sub_cat_Id is a PK column, sol_cat_id is a FK column. I am looking for an output based on FK Column(sol_cat_id). What I mean is...

Table Data Looks Like...

sol_sub_cat_Id sol_cat_id sol_sub_cat_item sol_sub_cat_sequence

-------------- ---------- ---------------- --------------------

1 2 Features 1

2 2 Programs 2

3 2 Users/Where 3

4 2 Advantages 4

5 2 Public Access 5

6 2 Security 6

7 2 Demo 7

8 3 Features 1

9 3 Programs 2

10 3 Users/Where 3

11 3 Advantages 4

12 3 Public Access 5

13 3 Security 6

14 3 Demo 7

Output Data should be...

sol_sub_cat_Id sol_cat_id sol_sub_cat_item sol_sub_cat_sequence Next_sequence

-------------- ---------- ---------------- -------------------- -------------

1 2 Features 1 2

2 2 Programs 2 3

3 2 Users/Where 3 4

4 2 Advantages 4 5

5 2 Public Access 5 6

6 2 Security 6 7

7 2 Demo 7 1

8 3 Features 1 9

9 3 Programs 2 10

10 3 Users/Where 3 11

11 3 Advantages 4 12

12 3 Public Access 5 13

13 3 Security 6 14

14 3 Demo 7 8

Please carefully check the output result. The last column (Next_sequence) in the output is the value of the sol_sub_cat_Id column in next row of sol_cat_id column group(2 or 3). Once the sol_sub_cat_Id ends for that group(2 or 3), then it should use the starting value sol_sub_cat_Id of that group(2 or 3).

For group 2:- the last row is "7 2 Demo 7 1"; here the last column value 1 should be the sol_sub_cat_Id of first row(1 2 Features 1) of group 2.

For group 3:- the last row is "14 3 Demo 7 8"; here the last column value 1 should be the sol_sub_cat_Id of first row(8 3 Features 1) of group 3.

I hope you got the issue.

thanks!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

arr.nagaraj (1/20/2010)
@jeff,
His sample result has 19,20 and after that 8,9. So I guessed he felt he needs it in the order in which Database stores ( or unordered scan resultorder ).

Yep... I know that. There's a secondary column in there that qill maintain the correct sort order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

nareshmca (1/20/2010)
Thanks for your reply. Sorry for the confusion.
First i have to tell one thing, sol_sub_cat_Id is a PK column, sol_cat_id is a FK column. I am looking for an output based on FK Column(sol_cat_id). What I mean is...
Table Data Looks Like...
sol_sub_cat_Id sol_cat_id sol_sub_cat_item sol_sub_cat_sequence
-------------- ---------- ---------------- --------------------
1 2 Features 1
2 2 Programs 2
3 2 Users/Where 3
4 2 Advantages 4
5 2 Public Access 5
6 2 Security 6
7 2 Demo 7
8 3 Features 1
9 3 Programs 2
10 3 Users/Where 3
11 3 Advantages 4
12 3 Public Access 5
13 3 Security 6
14 3 Demo 7
Output Data should be...
sol_sub_cat_Id sol_cat_id sol_sub_cat_item sol_sub_cat_sequence Next_sequence
-------------- ---------- ---------------- -------------------- -------------
1 2 Features 1 2
2 2 Programs 2 3
3 2 Users/Where 3 4
4 2 Advantages 4 5
5 2 Public Access 5 6
6 2 Security 6 7
7 2 Demo 7 1
8 3 Features 1 9
9 3 Programs 2 10
10 3 Users/Where 3 11
11 3 Advantages 4 12
12 3 Public Access 5 13
13 3 Security 6 14
14 3 Demo 7 8
Please carefully check the output result. The last column (Next_sequence) in the output is the value of the sol_sub_cat_Id column in next row of sol_cat_id column group(2 or 3). Once the sol_sub_cat_Id ends for that group(2 or 3), then it should use the starting value sol_sub_cat_Id of that group(2 or 3).
For group 2:- the last row is "7 2 Demo 7 1"; here the last column value 1 should be the sol_sub_cat_Id of first row(1 2 Features 1) of group 2.
For group 3:- the last row is "14 3 Demo 7 8"; here the last column value 1 should be the sol_sub_cat_Id of first row(8 3 Features 1) of group 3.
I hope you got the issue.
thanks!

So, did you ever get this issue resolved or do you still need help?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nareshmca SSC Rookie Points: 28 More actions · Answer 10

nareshmca

SSC Rookie

Points: 28

February 6, 2010 at 11:25 am

#1115036

Yes Jeff,

this issue was not resolved yet.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

Sorry... made a mistake and had to erase this post. I'll be back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

In case someone needs it, here's the test data I used...

--===== Conditionally drop the test table in case we need to rerun

IF OBJECT_ID('TempDB..#TestTable') IS NOT NULL

DROP TABLE #TestTable;

GO

--===== Create and populate the test table. This is NOT a part of the solution.

CREATE TABLE #TestTable

(

sol_sub_cat_Id INT,

sol_cat_id INT,

sol_sub_cat_item VARCHAR(30),

sol_sub_cat_sequence INT

);

INSERT INTO #TestTable

(sol_sub_cat_Id, sol_cat_id, sol_sub_cat_item, sol_sub_cat_sequence)

SELECT '1','2','Features' ,'1' UNION ALL

SELECT '2','2','Programs' ,'2' UNION ALL

SELECT '3','2','Users/Where' ,'3' UNION ALL

SELECT '4','2','Advantages' ,'4' UNION ALL

SELECT '5','2','Public Access','5' UNION ALL

SELECT '19','2','Security' ,'6' UNION ALL

SELECT '20','2','Demo' ,'7' UNION ALL

SELECT '8','3','Features' ,'1' UNION ALL

SELECT '9','3','Programs' ,'2' UNION ALL

SELECT '10','3','Users/Where' ,'3' UNION ALL

SELECT '11','3','Advantages' ,'4' UNION ALL

SELECT '12','3','Public Access','5' UNION ALL

SELECT '30','3','Security' ,'6' UNION ALL

SELECT '35','3','Demo' ,'7'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

Sorry... I missed copying the last criteria which broke the code. The following works and I'm taking CPR (cut, paste, replace) lessons tomorrow.. 😛

WITH

cteNumbered AS

(

SELECT ROW_NUMBER() OVER (ORDER BY sol_cat_id, sol_sub_cat_sequence) AS RowNum,

sol_sub_cat_Id, sol_cat_id, sol_sub_cat_item, sol_sub_cat_sequence

FROM #TestTable

)

SELECT lo.sol_sub_cat_Id, lo.sol_cat_id, lo.sol_sub_cat_item, lo.sol_sub_cat_sequence,

ISNULL(hi.sol_sub_Cat_Id,(SELECT n1.sol_sub_Cat_Id FROM cteNumbered n1 WHERE n1.sol_cat_id = lo.sol_cat_id AND n1.sol_sub_cat_sequence = 1 ))

AS NextSequence

FROM cteNumbered lo

LEFT OUTER JOIN cteNumbered hi

ON lo.RowNum + 1 = hi.RowNum

AND lo.sol_cat_id = hi.sol_cat_id

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David McKinney SSChampion Points: 10472 More actions · Answer 14

Sorry to come in so late....this might have been useful a few posts ago.

...but I see yours is pretty much the same, Jeff.

Linking to the previous row[/url]