Start with specific Row - SQL Server Forums

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)

Start with specific Row

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-06-08 : 10:11:49

I need to do a select statement that will take all records from a table but will start with a specific one. Is there a way to do that.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 10:13:13

Is it this ?

select *
from   yourtable
where  cola > 'somevalue'
order by cola

if not, post your table strucute, some sample data and the result that you want

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-08 : 10:14:15

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-06-08 : 10:16:29

What it is is an image gallery that scrolls through uploaded pictures. The structure is..

tblPictures
- PictureID
- PictureCaption
- PictureFile

Right now I just do a select * from tblPictures.

The tricky part is that you access the gallery from another page by clicking on a picture which is randomly generated by this same pool of pictures. So I want the first picture to come up in the gallery to be the same picture you clicked on from the original page. I had set up somethign like that...

Select * from tblPictures where PictureID = '" & PictureID & "'

however, it only produced one picture in the result set. I need to to start with that specific picture, but still include them all.

Does that make sense?

Thanks...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 10:18:23

are you retrieving the PictureID in accending order ?

Select * from tblPictures where PictureID >= '" & PictureID & "'

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-06-08 : 10:23:42

I'm getting all the pictures then displaying them in order of their ID number. The problem is that it always starts with the first picture. I want it to start with a specific one, yet still include them all in the result set.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 10:26:15

"include them all"
you mean you want the result to begin with the PictureID you specify and you still want to show all the records ?

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-06-08 : 10:27:06

Yes, they all need to be included, I just need it to begin with one that is specified.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 10:27:50

add the order by clause

order by case when PictureID < @PictureID then 1 else 0, PictureID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-08 : 10:28:55

You should post some sample data and the result you want
Refer this http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-06-08 : 10:39:01

It gave me an error, maybe I'm not doing it right.

"SELECT * FROM tblPictures order by case when PictureID < '" & PictureID & "' then 1 else 0, PictureID "

Does that look right.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-08 : 10:42:44

if pictureid is varchar datatype you need to use 'b' and 'a' instead of 1 and 0

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 10:43:05

sorry, my mistake. Missed out the end.

"SELECT * FROM tblPictures order by case when PictureID < '" & PictureID & "' then 1 else 0 end, PictureID "

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 10:46:33

quote:
Originally posted by madhivanan

if pictureid is varchar datatype you need to use 'b' and 'a' instead of 1 and 0

Madhivanan

Failing to plan is Planning to fail

that's not necessary.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-08 : 10:49:26

>>that's not necessary.

Yes it is. Thanks

Madhivanan

Failing to plan is Planning to fail

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-06-08 : 11:05:04

I tried that and it gave no errors, but started the result set from the beginning, not the specified row.

By the way, the PictureID field is an int field.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 11:16:12

Try this. Change the value of @PictureID

declare @tblPictures table
(
    PictureID	int
)

insert into @tblPictures
select	8	union all
select	5	union all
select	6	union all
select	7	union all
select	4

declare @PictureID	int

select	@PictureID = 5

select	*
from	@tblPictures
order by case when PictureID < @PictureID then 1 else 0 end, PictureID

/* RESULT
PictureID   
----------- 
5
6
7
8
4
*/

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-06-08 : 12:10:44

I will give it a try, thanks...

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-06-08 : 13:21:52

Hey that worked great. thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources