Getting the above and below rows in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Getting the above and below rows in a table

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 07:30:14

Hi,
I am having a table Products. Now I have to write a query to get the above and below rows along with row for which prod-id given.

The Prod table has Prod-id,Prodname, category

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-09 : 08:04:06

quote:
Originally posted by vmurali

Hi,
I am having a table Products. Now I have to write a query to get the above and below rows along with row for which prod-id given.

The Prod table has Prod-id,Prodname, category

What do u mean by above & below? how much above and below

Post sample data, expected output.

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-09 : 08:06:40

quote:

how much above and below

Playing with 2 meaning words..

Chirag

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 08:21:04

The row prior and row next for a given row

quote:
Originally posted by harsh_athalye

quote:
Originally posted by vmurali

Hi,
I am having a table Products. Now I have to write a query to get the above and below rows along with row for which prod-id given.

The Prod table has Prod-id,Prodname, category

What do u mean by above & below? how much above and below

Post sample data, expected output.

Harsh Athalye
India.
"Nothing is Impossible"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-09 : 08:44:54

vmurali,

>> The row prior and row next for a given row

For u it may be some rows and u may c those rows. but v cannot c the rows.
The SQL server doesn't have a (or precisely speaking, it cannot be guaranteed of) particular order by which u get the record set.

The Prior or Next is different in different contexts.
If it not is ordered (sorted) , then u will get the records in one way.
If sorted by a column, a different way, If by another column some other way.

Anyway, this is possible in Front End.
Also tell what is ur requirement.

Is it a sorted records set ? Is there a query to get ur records ?

Srinika

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 08:53:32

Ok Keeping that the rows are in sorted order. How to get the rows prior and next to a particular row.

quote:
Originally posted by Srinika

vmurali,

>> The row prior and row next for a given row

For u it may be some rows and u may c those rows. but v cannot c the rows.
The SQL server doesn't have a (or precisely speaking, it cannot be guaranteed of) particular order by which u get the record set.

The Prior or Next is different in different contexts.
If it not is ordered (sorted) , then u will get the records in one way.
If sorted by a column, a different way, If by another column some other way.

Anyway, this is possible in Front End.
Also tell what is ur requirement.

Is it a sorted records set ? Is there a query to get ur records ?

Srinika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-09 : 10:01:35

Why do you want to get the output like this?

Madhivanan

Failing to plan is Planning to fail

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-09 : 10:21:14

What is ur front end ? r u using ADO ? if so use MoveNext, MovePrevious.

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-09 : 11:13:23

@prod_id is the current value of @prod_id

-- previous row assuming ascending order of prod_id
select top 1 * from prod where prod_id < @prod_id order by prod_id

or

-- next row assuming ascending order of prod_id
select top 1 * from prod where prod_id > @prod_id order by prod_id

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-10 : 02:04:38

if i want to get the both statements together can i use union

quote:
Originally posted by khtan

@prod_id is the current value of @prod_id

-- previous row assuming ascending order of prod_id
select top 1 * from prod where prod_id < @prod_id order by prod_id

or

-- next row assuming ascending order of prod_id
select top 1 * from prod where prod_id > @prod_id order by prod_id

KH

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-10 : 02:08:23

Yeah somthing like this ..


select top 1 * from prod where prod_id < @prod_id order by prod_id
Union All 
select  * from prod where prod_id = @prod_id order by prod_id
Union All 
select top 1 * from prod where prod_id > @prod_id order by prod_id

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-10 : 03:16:26

You can't use ORDER BY with UNION directly.


select * from (select top 1 * from prod where prod_id < @prod_id order by prod_id) a
Union All 
select * from (select  * from prod where prod_id = @prod_id order by prod_id) b
Union All 
select * from (select top 1 * from prod where prod_id > @prod_id order by prod_id) c

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-10 : 03:25:41

quote:

You can't use ORDER BY with UNION directly.

Aww.. Thanks for the information

Chirag

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-10 : 03:32:54

but if i want to get the 2,3,4 record then this query doesn't seem to work as its returns the first record but i want it to return me only 2nd record

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-10 : 03:39:22

quote:
Originally posted by vmurali

but if i want to get the 2,3,4 record then this query doesn't seem to work as its returns the first record but i want it to return me only 2nd record

didnt understood you requirement Post same sample data

Chirag

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-10 : 04:43:55

This seems to be working

select top 1 * from product1 where [Product-id]=(select max([Product-Id]) from (select * from Product1 where [Product-Id]<1043) as a) union
select * from product1 where [Product-id]=1043 union
select top 1 * from product1 where [Product-id]=(select min([Product-Id]) from (select * from Product1 where [Product-Id]>1043) as b)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-10 : 12:45:42

Are you using fornt end application? If so, make use of recordset's movenext and moveprevious

Madhivanan

Failing to plan is Planning to fail

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-10 : 14:00:30

quote:
Originally posted by madhivanan

Are you using fornt end application? If so, make use of recordset's movenext and moveprevious

Madhivanan

Failing to plan is Planning to fail

quote:
Originally posted by Srinika

What is ur front end ? r u using ADO ? if so use MoveNext, MovePrevious.

Srinika

Srinika

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-11 : 00:14:22

Personally I would not be using 'TOP 1' type of queries because it implies you don't know your data well enough to exactly specify what you want. A few questions you need to ask (yourself):
a) How many rows either side do you want
b) Is productid the primary key or otherwise unique?
c) If not, what if your initial select returns more than one row?
d) or no rows
e) If not, what if your 'previous' and 'next' criteria also return more that one row?
f) or no rows?

In the easy case you need something like (this is typed in as I think - it won't compile)

select * from product
where productid in
(
select
(
select productId from prod
where
productId=whatever
) pid
union
(
select max(productId) from product where productID<whatever
)
union
(
select min(productId) from product where productID>whatever
)
)
order by product id

This assumes uniqueness on prodid. It's similar to your 'seems to be working' solution although guarantees order which yours wont (no order by = no guarantees)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-11 : 12:47:08

quote:
Originally posted by Srinika

quote:
Originally posted by madhivanan

Are you using fornt end application? If so, make use of recordset's movenext and moveprevious

Madhivanan

Failing to plan is Planning to fail

quote:
Originally posted by Srinika

What is ur front end ? r u using ADO ? if so use MoveNext, MovePrevious.

Srinika

Srinika

How did I forget to read your previous replies?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources