0

I want to limit selected all except 1st row.
Id is int, but not UNIQUE,
checkIn is date

SELECT * FROM table  
Order by property_id, checkIn DESC 
LIMIT 2, (SELECT Count(property_id)-1 FROM table)

Both Queries are working. but then I put them together i get

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT Count(property_id) FROM table)' at line 1

Maybe here is universal key for all rows at LIMIT 1, ALL ?

Ravinder Reddy
  • 22,363
  • 6
  • 46
  • 76
Laimonas
  • 36
  • 7

5 Answers5

1

If you need all rows except the first one, try this:

SELECT * FROM TBL LIMIT 1, 18446744073709551615;

18446744073709551615 is the recommend as a value as in the mysql docs and the maximum of a unsigned bigint.

There was a similar question.

Community
  • 1
  • 1
tjati
  • 5,069
  • 3
  • 31
  • 49
1

For completeness, here's how you can do it with a prepared statement:

SET @skip=1; 
SET @numrows=(SELECT Count(property_id)-1 FROM table);
PREPARE STMT FROM 'SELECT * FROM table Order by property_id, checkIn DESC LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

Although, if it's a InnoDB table, I wouldn't recommend to do a SELECT COUNT(*) on it repeatedly. Unlike MyISAM it doesn't store a row count in the table. Therefore counting on InnoDB can be slow when it has lots of rows.

P.S.: Note, that you have to use limit 1, how_many instead of limit 2, how_many when you want to skip one row, since it starts counting from 0, not from 1.

fancyPants
  • 46,782
  • 31
  • 80
  • 91
0

Johan already mentioned here

You can not use a subquery as a LIMIT argument. Limit argument should be an INTEGER. You subquery returns, wel... basically, a table.

From the MySQL manual

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

Edit

Try like this

but i'm not tested

DECLARE offset bigint;
SELECT Count(property_id)-1 INTO offset FROM table;
SELECT * FROM table Order by property_id, checkIn DESC LIMIT 2,offset;
Community
  • 1
  • 1
Vignesh Kumar A
  • 26,578
  • 11
  • 57
  • 101
  • So here is no way to achieve that? I mean display all items except first one, if count of rows, is changing all the time? Edit: I mean by using only MySQL in one query. – Laimonas Apr 09 '14 at 07:00
  • It give me syntax error just at start for DECLARE'ing offset – Laimonas Apr 09 '14 at 07:12
  • @Laimonas Can you check now – Vignesh Kumar A Apr 09 '14 at 07:14
  • I tryed that after i get error at first. kinda same error `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE offset bigint' at line 1 ` – Laimonas Apr 09 '14 at 07:38
  • @Laimonas you should not count before the values if you just need all but not the first rows. My solution is more performant. – tjati Apr 09 '14 at 07:43
0

I agree with @omeinusch's answer, but also offer the following alternative:

SELECT * FROM table t JOIN (
  SELECT   property_id, checkIn
  FROM     table
  ORDER BY property_id, checkIn DESC
  LIMIT    2,1
) s ON (t.property_id > s.property_id)
    OR (t.property_id = s.property_id AND t.checkIn <= s.checkIn)
Community
  • 1
  • 1
eggyal
  • 113,121
  • 18
  • 188
  • 221
0

If you have a unique id column, then you could do this:

select * FROM table 
where id <> (SELECT id FROM table Order by property_id, checkIn DESC LIMIT 1)

Otherwise, assuming property_id and checkIn are unique,

select * FROM table 
where concat(property_id,'|',checkIn) <> 
     (SELECT concat(property_id,'|',checkIn)  FROM table Order by property_id, checkIn DESC LIMIT 1)
Firoz
  • 1
  • 2