Hi there,
how can i fetch 3 records from table ? let me clear, suppose I've id=5 then i would like to fetch records whose id would be 4,5,6.
that means, i want to fetch previous and next records of current record.
what would be the query ?

Let's take id = 39

SELECT idKey, otherField FROM (

( SELECT idKey, otherField FROM myTab WHERE idKey > 39 ORDER BY idKey ASC LIMIT 1 )
UNION ALL
( SELECT idKey, otherField FROM myTab WHERE idKey = 39 LIMIT 1 )
UNION ALL
( SELECT idKey, otherField FROM myTab WHERE idKey < 39 ORDER BY idKey DESC LIMIT 1 )

 ) t1

Using what database server??

and BTW SQL is NOT PHP.

Originally Posted by vectorialpx

Let's take id = 39

SELECT idKey, otherField FROM (

( SELECT idKey, otherField FROM myTab WHERE idKey > 39 ORDER BY idKey ASC LIMIT 1 )
UNION ALL
( SELECT idKey, otherField FROM myTab WHERE idKey = 39 LIMIT 1 )
UNION ALL
( SELECT idKey, otherField FROM myTab WHERE idKey < 39 ORDER BY idKey DESC LIMIT 1 )

 ) t1

Why the UNION query?

WHERE column >= number-1 OR column <= number+1

will suffice.

Only if you can guarantee an arithmetic sequence diff 1 and no members missing.

Originally Posted by PaulW

Only if you can guarantee an arithmetic sequence diff 1 and no members missing.

That's quite true, but as per the OP's question

i would like to fetch records whose id would be 4,5,6.

It would be reasonable to expect such a scenario to exist.

It's about records so, when we say ID we all think at some auto_increment primary key field. You cannot write your SQL based on some presumption (that you'll never delete from that thable)

(that you'll never delete from that thable)

Yep BUT you can only answer questions based on the information given.

In your UNION queries you have assumed that MySQL is the database server by your use of a LIMIT clause in the query.

The OP specified records with id's of 4,5 & 6 and did not detail what server was being used


Also "deleting" information in some scenarios may not actually remove rows/records from a table.
In an accounting system for instance, invoice or ledger records HAVE to be maintained as numerically sequential and should be simply flagged as deleted/tombstoned, and not actually removed from the database to allow for an audit trail.

SQL is NOT PHP

yes it's not. I missed database forum.

Why the UNION query?

i have no idea about UNION.
and 4,5,6 is for example. actual records can be like 3,6,9 and i'll be having 6 with me and have to get 3 and 9 from it.
I missed many things here. I'm using mysql. this is not accounting system i don't want to audit anything here. once something deleted it's gone. at most i'm tracking down title of the record deleted and which user/admin deleted it. planning to use trigger for this purpose. tracking data will be stored in another table. and this is so irrelevant stuff i mentioned here.

Originally Posted by phazorRise

i have no idea about UNION.

You have to have same columns to get results (and to avoid errors) and all selects are accumulated, same as a simgle one. You can select form more than one table, but maintain the column alias, like this

You have 3 columns, named c1, c2, c3.
Every subselect must return those 3 columns, named the same, even if some are simple strings.

SELECT c1, c2, c3 FROM (

( SELECT someField as c1, another as c2, other c3 FROM someTable )
UNION ALL
( SELECT SUM(field1) c1, anotherField c2, lastOne c3 FROM anotherTable )
UNION ALL
( SELECT field1 c1, anotherField c2, 'simpleString' as c3 FROM anotherTable )

) t1

You can see some examples reading mysql website

actual records can be like 3,6,9

Of course thay can! Some system that uses keys "maintained as numerically sequential" is just wrong... it's just not designed by a programmer. Primary keys are meant to be unique, not to identify some imaginary sequence. If for some reasons those systems exists, they are just exceptions.


edit // Oh, I assumed that it's about MySql because PHP working with other DBMS is just a rare thing. So, yes Chris, I made some suppositions I didn't have no reason or intention to upset you but (sorry if I did that), I just wrote my opinion.

it's just not designed by a programmer.

Thank goodness for that!

Programmers who think they are also DBA's are the bane of a DBA's life.

And it is nothing at all to do with keys Primary or Foreign.

But if the tax office audits your system and the invoice numbers or ledger entries are NOT sequentially numbered and ALL records exist including the cancelled/deleted ones, they WILL be looking into your accounts system much more closely.

i found following query does the thing i actually want. And it's quite simple also.

(SELECT id FROM city
   WHERE id < 5
    ORDER BY id DESC LIMIT 1)
UNION
(SELECT id FROM city
   WHERE id >= 5
   ORDER BY id LIMIT 2)

so suppose i have id=5 in my hand and records present in table as -

id otherF
2     -
5     - 
7     -

so above query returns successful result also.