SELECT * help needed.

[Simon]

Hi,

If I have a lot of articles, all with a unique IDs.

First I would like to search for that ID, I could do

SELECT * from ARTICLE where ID = xx

But I also want to display the 5 articles before and after that article.

SELECT * from ARTICLE where ID > xx LIMIT 0, 10
and
SELECT * from ARTICLE where ID < xx LIMIT 0, 10

(I choose a limit of 10 in case the ID is one of the first one or one of the
last one, that way I will always have at least 10 articles).

Is it possible to do the above in one single query?

Should I even bother doing that? does it make the whole operation faster to
do it that way?

Many thanks.

Simon

[Gerard]

Re: SELECT * help needed.

Providing that u know what xx is there two other needed variables are
the xx-5 and xx+5

so then

$start = $xx-5;
$stop = $xx+5;

SELECT * FROM Articles WHERE id >= $start AND id <= $stop;

I think this is what you're looking for.

Rgds,
Gerard

[Marcin Dobrucki]

Re: SELECT * help needed.

Simon wrote:
[color=blue]
> If I have a lot of articles, all with a unique IDs.
> First I would like to search for that ID, I could do
> SELECT * from ARTICLE where ID = xx
> But I also want to display the 5 articles before and after that article.
> SELECT * from ARTICLE where ID > xx LIMIT 0, 10
> and
> SELECT * from ARTICLE where ID < xx LIMIT 0, 10
> (I choose a limit of 10 in case the ID is one of the first one or one of the
> last one, that way I will always have at least 10 articles).
> Is it possible to do the above in one single query?
> Should I even bother doing that? does it make the whole operation faster to
> do it that way?[/color]

SELECT * FROM article WHERE ( x-5 < id and id < x+5 )

Or pick borders as you like.

/m

[Oli Filth]

Re: SELECT * help needed.

Gerard wrote:[color=blue]
> Providing that u know what xx is there two other needed variables are
> the xx-5 and xx+5
>
> so then
>
> $start = $xx-5;
> $stop = $xx+5;
>
> SELECT * FROM Articles WHERE id >= $start AND id <= $stop;
>
> I think this is what you're looking for.
>[/color]

This assumes, of course, that there are no gaps in the sequence of
IDs...

--
Oli

[Simon]

Re: SELECT * help needed.

[color=blue]
> Providing that u know what xx is there two other needed variables are
> the xx-5 and xx+5
>
> so then
>
> $start = $xx-5;
> $stop = $xx+5;
>
> SELECT * FROM Articles WHERE id >= $start AND id <= $stop;
>
> I think this is what you're looking for.
>
> Rgds,
> Gerard[/color]

I know what XX is but unfortunately the unique ID are not in sequence.

So I could have articles with ID

1, 4, 5, 6, 10, 11, and so on...

I don't have all the article numbers, (in the case above I don't have ID 2,
3, 7, 8 and 9).

So doing your select would not work.

Thanks

Simon

[Simon]

Re: SELECT * help needed.

[color=blue]
> Gerard wrote:[color=green]
>> Providing that u know what xx is there two other needed variables are
>> the xx-5 and xx+5
>>
>> so then
>>
>> $start = $xx-5;
>> $stop = $xx+5;
>>
>> SELECT * FROM Articles WHERE id >= $start AND id <= $stop;
>>
>> I think this is what you're looking for.
>>[/color]
>
> This assumes, of course, that there are no gaps in the sequence of
> IDs...
>[/color]

You are right, there are gaps in the sequence.

Simon

[Oli Filth]

Re: SELECT * help needed.

Simon wrote:[color=blue][color=green]
> > Providing that u know what xx is there two other needed variables are
> > the xx-5 and xx+5
> >
> > so then
> >
> > $start = $xx-5;
> > $stop = $xx+5;
> >
> > SELECT * FROM Articles WHERE id >= $start AND id <= $stop;
> >
> > I think this is what you're looking for.
> >
> > Rgds,
> > Gerard[/color]
>
> I know what XX is but unfortunately the unique ID are not in sequence.
>
> So I could have articles with ID
>
> 1, 4, 5, 6, 10, 11, and so on...
>
> I don't have all the article numbers, (in the case above I don't have ID 2,
> 3, 7, 8 and 9).
>
> So doing your select would not work.
>[/color]

(SELECT * FROM Articles WHERE id >= $x ORDER BY id LIMIT 6)
UNION
(SELECT * FROM Articles WHERE id < $x ORDER BY id DESC LIMIT 5)

It's possible that the syntax isn't 100% correct, but you get the
idea...

--
Oli

[Ewoud Dronkert]

Re: SELECT * help needed.

Ask in a Mysql related newsgroup. For example, and preferably,
comp.databases. mysql.

--
E. Dronkert

[Simon]

Re: SELECT * help needed.

[color=blue]
> SELECT * FROM article WHERE ( x-5 < id and id < x+5 )
>
> Or pick borders as you like.[/color]

I should have mentioned that the IDs have gaps.

[color=blue]
>
> /m[/color]

Simon

[Ian B]

Re: SELECT * help needed.

Not quite what you asked for but

select num, abs(num - $reqd ) as srt from t1 order by srt limit 11

will give you your record plus the nearest 10 records to it

Just a thought.

Ian

[Tim Roberts]

Re: SELECT * help needed.

"Simon" <spambucket@exa mple.com> wrote:[color=blue]
>Hi,
>
>If I have a lot of articles, all with a unique IDs.
>
>First I would like to search for that ID, I could do
>
>SELECT * from ARTICLE where ID = xx
>
>But I also want to display the 5 articles before and after that article.
>
>SELECT * from ARTICLE where ID > xx LIMIT 0, 10
>and
>SELECT * from ARTICLE where ID < xx LIMIT 0, 10[/color]

This is incorrect. SQL tables have no inherent ordering. Thus, the first
query will get 10 records with ID numbers larger than xx, but there is
absolutely no guarantee that they will be the records immediately above xx,
nor that they will be ordered in any way.

Given the limitations you described, there is no reliable way to do this
other than:

SELECT * FROM article WHERE ID < xx ORDER BY id DESC LIMIT 10
UNION
SELECT * FROM article WHERE ID > xx ORDER BY id LIMIT 10;
--
- Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.