CODEWITHSUNDEEP
mysql
user1844626
user1844626

Reputation: 1868

How can select all records excepting first 3 records?

I have a column

id
-----
1
32
3
6
5
22
54
21

Ques1: How can I select all records from the column excepting the first 3 records ?
Ques2: How can select last 3 records ?

-Thanks.

Upvotes: 2

Views: 2138

Answers (4)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39981

You basically need to put such queries into stored procedures due to some limitations on LIMIT. You can't use sub selects or variables in plain sql. In stored procedures you can use variables.

This works, unfortunately I can't show it in sqlfiddle cause they seem to have limited support for stored procedures.

drop procedure if exists all_but_3;

delimiter //
create procedure all_but_3()
begin
   declare v_max bigint unsigned default ~0;
   select * from your_table limit 3, v_max;
end//

delimiter ;

drop procedure if exists last_3;
delimiter //
create procedure last_3()
begin
   declare v_max bigint;
   declare v_mid bigint;
   select count(*) from your_table into v_max;
   set v_mid := v_max - 3;
   select * from your_table limit v_mid, v_max;
end//

delimiter ;

call all_but_3();
call last_3();

Elaboration on InnoDB Clustered Indexes

After discussions in one of the other answers with @fthiella I've decided to elaborate some on how this can work.

A table using InnoDB as engine will always have a clustered index. Always. It's they way data is stored in InnoDB and it's not in any way possible to create a table without a clustered index.

InnoDB will choose the primary key if there is one or the first unique index with all columns set to not null. If no such index exists InnoDB will create a hidden column with a row id. This row id works similar to auto increment and if it helps to think about it as a invisible column with auto increment I think that is fine.

Further will InnoDB return rows according to the index used. It will always use some index (only way to retrieve data is to use either a secondary index, the clustered index or a combination) so in the case where there are no explicitly created indexes rows are returned by the hidden clustered index.

This means that a query against a table with no primary key and no unique indexes with all columns set to not null and no ORDER BY will return rows in the order they where inserted.

This is the case for this question and the base for mine and many other answers.

I don't mean to say this is a good way to work with the data. Here are some things that you should think about before using this solution:

  • If a index that can be used as a clustered index is ever created the table will be rewritten to use that index and by doing so order the data on disk. If the index is later dropped the original insert order is lost and cannot be retrieved.
  • If a index is created, even if it's not unique, it could be chosen by the optimizer to be used and the rows will be ordered by that index instead.

All this is documented and for 5.5 it's the 3rd bullet points on this page

Upvotes: 3

suresh gopal
suresh gopal

Reputation: 3156

Try this:

totalCounts = SELECT COUNT(*) FROM tbl;

SELECT * FROM tbl LIMIT 3,totalCounts
# Retrieve rows 4 to number of table rows

Upvotes: -1

tristan
tristan

Reputation: 409

  1. select all except the first 3 records:

    select id from table1 limit 3, 18446744073709551615;

  2. select the last 3 records:

    select a.id from (select @row_num:=@row_num+1 as RowNumber,id from table1, (select @row_num:=0) x order by RowNumber desc) as a limit 3;

Upvotes: 3

Corbin
Corbin

Reputation: 33447

Use LIMIT

For #1, you theoretically need to know the number of rows, but you can just throw a huge number in instead:

SELECT bleh FROM blah LIMIT 3, 18446744073709551615

(18446744073709551615 is the max number possible -- thanks to alex vasi for pointing me to this)

For #2, you do actually need to know the number of records:

SELECT bleh FROM blah LIMIT NUM-3, 3

You'll have to get the number of rows from a separate query (which should be where NUM is).

If there's a criteria you can sort on, you can actually do the second query without doing a full count:

SELECT bleh FROM blah ORDER BY field DESC LIMIT 3

I'm assuming that's not the case though.

(Another option if you don't want to do a count on the table is to just pull all of the data and ignore the first or last 3 rows in code-land [PHP, C, etc].)

Upvotes: 1

Related Questions