Mysql select all but first and last row

Question

I want the database to show all the rows, except for the first and last ones, since I have CSS code for them.

I tried this:

SELECT * FROM db 
WHERE 
keywords LIKE '%example%' LIMIT 9999 OFFSET 1
 AND 
keywords LIKE '%example%' DESC LIMIT 9999 OFFSET 1

Since the row number may increase I can't write an exact number.

Possible duplicate of [MySQL: How to select all rows from a table EXCEPT the last one](https://stackoverflow.com/questions/315621/mysql-how-to-select-all-rows-from-a-table-except-the-last-one) — Liam Lutton, Jul 22 '17 at 04:10
It is strongly advisable that you handle this with PHP code, to avoid making 2 or more request — user10089632, Jul 22 '17 at 04:10
Hi @Kakotas, if the provided answer below solved your issue, could you mark it as accepted? — Cyril Graze, Jul 28 '17 at 04:05

score 4 · Accepted Answer · answered Jul 22 '17 at 04:21

There really is no reason to complicate your query by trying to snip off these values at the SQL level, you could do:

$results = $db->query( /* regular query with all results */ );

array_pop($results);
array_shift($results);

// Now use $results, which now contains only the "middle" content

If you really want it at the DB level, you can use:

SELECT * FROM db 
WHERE keywords LIKE '%example%' 
AND id <> (SELECT MAX(ID) FROM TABLE)
AND id <> (SELECT MIN(ID) FROM TABLE)

I find this to be the most sensible answer – W.K.S Jul 22 '17 at 04:24 — W.K.S, Jul 22 '17 at 04:24

user10089632 · Answer 2 · 2017-07-22T04:19:58.360

0

You can try this for instance:

SELECT * FROM TABLE WHERE ID != (SELECT MAX(ID) FROM TABLE) LIMIT 1,9844674507370

But like I said in the comment : It is strongly advisable that you handle this with PHP code, to avoid making 2 or more requests

edited Jul 22 '17 at 04:19

answered Jul 22 '17 at 04:13

user10089632

5,216
1
26
34

score 0 · Answer 3 · answered Jul 22 '17 at 04:13

0

You can use UNION like as

SELECT * FROM db WHERE keywords LIKE '%example%' order by keywords ASC limit 1 UNION SELECT * FROM db WHERE keywords LIKE '%example%' order by keywords DESC limit 1;

answered Jul 22 '17 at 04:13

vjy tiwari

843
1
5
17

B. Desai · Answer 4 · 2017-07-22T04:21:25.930

0

You can do it without using LIMIT AND OFFSET

SELECT * FROM table_name WHERE id != (SELECT MAX(id) FROM table_name) AND id != (SELECT MIN(id) FROM table_name)

SELECT * FROM db 
WHERE 
keywords LIKE '%example%'
AND
id != (SELECT MAX(id) FROM db) 
AND 
id != (SELECT MIN(id) FROM db)

here id will be your auto increment key

edited Jul 22 '17 at 04:21

answered Jul 22 '17 at 04:17

B. Desai

16,414
5
26
47

score -1 · Answer 5 · answered Jul 22 '17 at 04:14

1st : Simple you can handle this thing in php like below . avoid two query

 $last_record =count($records)-1;
 $i=0;
   foreach($records as $key=>$row)
   {
      if($i==0){
           //apply the css
       }

      if($i== $last_record ){
           //apply the css
       }
   }

query :

SELECT * FROM db WHERE keywords LIKE '%example%'

Mysql select all but first and last row

5 Answers5