0

I'm writing a query to get records from MySQL table as below.

SELECT * FROM alerts

i have this columns id is primary and auto increment 

id,host,domain,alert_time

But i need to get records without last record of this table, anyone know how to write a MySQL query for that. Thank You

Suneth Kalhara
  • 1,211
  • 6
  • 20
  • 40
  • 1
    SQL tables represent unordered sets. There is no concept of "last" record (or "first" record or "second" record) unless another column specifies the ordering. Do you have such a column? When asking a question, you should include sample data and desired results. – Gordon Linoff Apr 19 '15 at 14:04
  • i have a primary key called id which is auto increment – Suneth Kalhara Apr 19 '15 at 14:10
  • Agreed with @GordonLinoff. Unless the exact meaning of "last record" is defined, the question is unclear, and will lead to diverse answers based on diverse interpretations. – Abhay Apr 19 '15 at 14:11
  • 1
    Duplicate of http://stackoverflow.com/questions/315621/mysql-how-to-select-all-rows-from-a-table-except-the-last-one – A l w a y s S u n n y Apr 19 '15 at 14:16
  • ^ @BeingSunny didn't found that before :) – Suneth Kalhara Apr 19 '15 at 14:18

2 Answers2

4

Try this way to get your MAX value of id column and then select all rows except it.

SELECT * FROM alerts WHERE ID != (SELECT MAX(ID) FROM alerts)
A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103
Manish Shukla
  • 1,355
  • 2
  • 8
  • 21
2

An alternative method is:

SELECT *
FROM alerts a
ORDER BY id DESC
LIMIT 1,99999999

I like Manish's answer better for removing the largest id, but there are circumstances where this would be appropriate. For instance, if you wanted all but the most recent three records, it is easier to do using this method.

(Note: The 99999999 is just a large number to get the rest of the rows.)

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @Gordon Linoff, Sir can you please explain about the large number i mean `LIMIT 1,99999999`? – A l w a y s S u n n y Apr 19 '15 at 14:29
  • @BeingSunny . . . I thought I did. The `1` is the offset. This starts at 0, so "1" means skip the first row. The 99999999 is the number of rows to fetch. The large number is big enough to get the rest of the rows. You have to put a constant there, so it is just a big number. – Gordon Linoff Apr 19 '15 at 14:56
  • @ Gordon Linoff, OMG, how i missed it `ORDER BY id DESC` on your query, that's why i was little confused about `limit 1,99999999`. Thanks for replying. – A l w a y s S u n n y Apr 19 '15 at 15:30