I haven't been able to write a SQL statement for this seemingly
trivial selection.

If column col of table T is [a,b,c,d,e]
how do I write

select * from T where col < 'c' limit 1
select * from T where col > 'c' limit 1

as a single query? Thanks,

--thelma

thelma@uwm.edu wrote:
>I haven't been able to write a SQL statement for this seemingly
> trivial selection.
>
> If column col of table T is [a,b,c,d,e]
> how do I write
>
> select * from T where col < 'c' limit 1
> select * from T where col > 'c' limit 1
>
> as a single query? Thanks,
>
> --thelma

SELECT
*
FROM t
WHERE col <> 'c'
LIMIT 1

On May 25, 6:04 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> the...@uwm.edu wrote:
> >I haven't been able to write a SQL statement for this seemingly
> > trivial selection.
>
> > If column col of table T is [a,b,c,d,e]
> > how do I write
>
> > select * from T where col < 'c' limit 1
> > select * from T where col > 'c' limit 1
>
> > as a single query? Thanks,
>
> > --thelma
>
> SELECT
> *
> FROM t
> WHERE col <> 'c'
> LIMIT 1

Don't you mean ORDER BY col LIMIT 2 ?

toby wrote:
> On May 25, 6:04 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> the...@uwm.edu wrote:
>> >I haven't been able to write a SQL statement for this seemingly
>> > trivial selection.
>>
>> > If column col of table T is [a,b,c,d,e]
>> > how do I write
>>
>> > select * from T where col < 'c' limit 1
>> > select * from T where col > 'c' limit 1
>>
>> > as a single query? Thanks,
>>
>> > --thelma
>>
>> SELECT
>> *
>> FROM t
>> WHERE col <> 'c'
>> LIMIT 1
>
> Don't you mean ORDER BY col LIMIT 2 ?

I don't think that would help. The order that the two single queries supply
their output is indeterminate, so there is no guarantee which two rows would
have been returned. Your adaptation would produce a and b, whereas the
second of the two posted queries whould have produced one of c or d.

thelma@uwm.edu wrote:
> I haven't been able to write a SQL statement for this seemingly
> trivial selection.
>
> If column col of table T is [a,b,c,d,e]
> how do I write
>
> select * from T where col < 'c' limit 1
> select * from T where col > 'c' limit 1
>
> as a single query? Thanks,
>
> --thelma

Not tested (and may not work as listed, but close):

SELECT col
FROM T
WHERE col < 'c'
ORDER BY col DESC
LIMIT 1
UNION
SELECT col
FROM T
WHERE col < 'c'
ORDER BY col ASC
LIMIT 1

Notes:

Data in SQL databases is always unordered. If you want sorted data, you
must use ORDER BY in your SQL statement.

You should never use SELECT *. Always identify the columns you are
selecting.



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================