CODEWITHSUNDEEP
mysqlselect
Aliskin
Aliskin

Reputation: 169

Get Prevision Next record in ONE ROW

I want get prev\next id in one row. If not value for next or prev rec its must be NULL.

mysql> select picsid  from pics where  albid_pics=15;
+--------+
| picsid |
+--------+
|    110 |
|    111 |
|    112 |
|    113 |
|    114 |
|    115 |
|    116 |
|    117 |
|    131 |
|    132 |
|    133 |
|    134 |
|    135 |
|    136 |
|    153 |
|    154 |
|    155 |
|    156 |
|    157 |
|    159 |
|    160 |
+--------+
21 rows in set (0.00 sec)

mysql>

1. if i do

(select picsid prv from pics where picsid<136 and albid_pics=15 order by prv desc limit 1)
union
(select picsid nxt from pics where picsid>136 and albid_pics=15 order by nxt asc limit 1);
+-----+
| prv |
+-----+
| 135 |
| 153 |
+-----+
2 rows in set (0.00 sec)

mysql>

But i want get qresult in 1 row,

2.

mysql> (select picsid prv from pics where picsid<160 and albid_pics=15 order by prv desc limit 1)  union  (select picsid nxt from pics where picsid>160 and albid_pics=15 order by nxt asc limit 1);
+-----+
| prv |
+-----+
| 159 |
+-----+
1 row in set (0.00 sec)

mysql>

If not value for next or prev record its must be NULL.

Whst this make?

Upvotes: 0

Views: 95

Answers (1)

KIKO Software
KIKO Software

Reputation: 16688

What you're looking for is quite simple. For instance, this:

SELECT (1+1) AS sum1,(2+2) AS sum2

will result in:

+------+------+
| sum1 | sum2 |
+------+------+
|    2 |    4 |
+------+------+

All you have to do is put your subqueries in there:

SELECT (SELECT picsid 
        FROM pics 
        WHERE picsid < 136 AND 
              albid_pics = 15 
        ORDER BY picsid DESC 
        LIMIT 1) AS previous,
        (SELECT picsid 
        FROM pics 
        WHERE picsid > 136 AND 
              albid_pics = 15 
        ORDER BY picsid ASC 
        LIMIT 1) AS `next`;

Note: next is a keyword in MySQL, hence the backticks.

Upvotes: 2

Related Questions