The story so far, with comments:
Michael DePhillips wrote:
> Hi,
>
> Does anyone have a clever way of returning; a requested value with one
> value less than that value, and one value greater than that value with
> one query.
>
> For example T1 contains
>
> ID
> 1234
> 1235
> 1236
> 1238
>
> select ID from T1 where ID = 1235 and ID<1235 and ID >1235 LIMIT 3
> (obviously this doesn't work) I would want to return....
>
> 1234
> 1235
> 1236
>
> or;
> select ID from T1 where ID = 1237 and ID<1237 and ID >1237 LIMIT 3 I
> would want
>
> 1236
> 1238
>
> I would be surprised if there was no way of doing this.....but then
> again, I'm often surprised....
Michael DePhillips also wrote:
<snip>
> ...the id may not always
> be one(1) value away. So the number one needs, somehow, to be replaced
> with a way to get the "next largest value " and the "previous less than"
> value.
>
> Sorry for the lack of precision in my prior post.
nigel wood wrote:
> What you want to do isn't possible in the form you want without
> subqueries. Which would you prefer:
Yes, I believe it is. See below.
> a) A single query returning one row of three columns containing nulls
> b) A single query using sub queries (MySQL 4.1+ only) returning upto
> three rows with one column
> c) multiple queries
>
> Nigel
Michael DePhillips wrote:
> Hi Nigel,
>
> A and B...please.
Douglas Sims wrote:
>
> I think this will do it, although it takes three queries.
>
> I'm assuming the id values are unique, even if there can be gaps (that's
> what you might get with an AUTO_INCREMENT field). If the values are not
> guaranteed to be unique then this may not give what you want (if there
> are multiple rows with the same value you're looking for, e.g.
> 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not
> three fives.)
>
> SELECT @id:=5;
> SELECT * FROM t1 WHERE id<(SELECT MAX(id) FROM t1 WHERE id<@id) ORDER BY
> id DESC LIMIT 1;
> SELECT * FROM t1 WHERE [EMAIL PROTECTED] LIMIT 1;
> SELECT * FROM t WHERE id>(SELECT MIN(id) FROM t1 WHERE id>@id) ORDER BY
> id ASC LIMIT 1;
I think you're on the right track, but this is unnecessarily complicated, and,
unfortunately, wrong. Given rows with the ids 3,4,5,6,9, it selects 3, 5, and 9
rather than 4, 5, and 6.
Look at the first query:
SELECT * FROM t1
WHERE id < (SELECT MAX(id) FROM t1 WHERE id<@id)
ORDER BY id DESC LIMIT 1;
The subquery finds the previous id (isn't that what we want?), then the outer
query selects all the ids that are *less than* the id we want and limits the
output to the largest one. In other words, we get the row prior to the row that
is prior to the row with the chosen id. You need an "=" in there somewhere.
To get the previous id:
SELECT MAX(id) FROM t1 WHERE id < @id;
To get the entire previous row:
SELECT * FROM t1 WHERE id < @id ORDER BY id DESC LIMIT 1;
or
SELECT * FROM t1 WHERE id = (SELECT MAX(id) FROM t1 WHERE id < @id);
Your last query is similar -- it selects the 2nd row after the middle one rather
than the next one.
> But as to putting that in one statement... it might be better just to do
> it as three.
There's always UNION. See below.
[EMAIL PROTECTED] wrote:
> Here's a single query version of Douglas's solution:
>
> select @id:=6;
>
> select distinct t.testid,
> (select max(testid) from t where testid < @id) as previousId,
> (select min(testid) from t where testid > @id) as nextId
> from t
> where [EMAIL PROTECTED]
>
> Donna
Well, this requires subqueries (mysql 4.1+), and only returns the ids (not the
other columns in those rows), but probably neither is a big deal. There is one
big problem, however: It returns nothing if the chosen id doesn't exist.
Michael specified that he wanted the rows before and after the chosen id, even
if no row has the chosen id.
Try this:
SET @id = 13;
To get just the ids, even when no row has id = @id:
(SELECT MAX(id) FROM t1 WHERE id < @id)
UNION DISTINCT
(SELECT id FROM t1 WHERE id = @id)
UNION DISTINCT
(SELECT MIN(id) FROM t1 WHERE id > @id)
ORDER BY 1;
To get the row contents, as well:
(SELECT * FROM t1 WHERE id < @id ORDER BY id DESC LIMIT 1)
UNION DISTINCT
(SELECT * FROM t1 WHERE id = @id)
UNION DISTINCT
(SELECT * FROM t1 WHERE id > @id ORDER BY id ASC LIMIT 1)
ORDER BY id;
(Whether those are each one query or three depends on how you feel about UNION,
I suppose.)
Now, that last query isn't very efficient. The first and last parts may not use
the index on id very well. You could improve on this significantly if you knew
the size of the largest gap in the sequence of ids (or at least an upper bound).
For example, if you were sure no 2 ids were farther apart than 12, you could
take that into account, like so:
SET @id = 2345;
SET @maxgap = 12;
(SELECT * FROM inits
WHERE id < @id AND id > @id - @maxgap
ORDER BY id DESC LIMIT 1)
UNION DISTINCT
(SELECT * FROM inits WHERE id = @id)
UNION DISTINCT
(SELECT * FROM inits
WHERE id > @id AND id < @id + @maxgap
ORDER BY id ASC LIMIT 1)
ORDER BY id;
As long as the largest gap is small relative to the total number of rows, the
index on id will almost certainly be used to speed up this version. It's a bit
of a hack, but it would work.
That said, it may well be faster to fetch just the ids with the first query,
then get the matching rows with a second query. In fact, that's almost
certainly the case if you can't or won't use the max gap hack.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
