MYSQL Where function

Question

I have a table made up of podcasts I have done, song, artist, title mix number date and etc. I am using a class with individual methods to display them in separate

My question is, how do you get desc rows? Like this (mix_number is an int)

 select mix_name, date where mix_number = MAX(mix_number) limit 1 //1st query

 select mix_name, date where mix_number = MAX(mix_number - 1)//this would be the second query

 select mix_name, date where mix_number = MAX(mix_number - 2)//3rd query

I dont want to hardcode the where clause with a number because I want it to update as I add more.

I am really asking is MAX(mix_number) or MAX(mix_number-1) proper? I cant get it to work this way

I hope this is understandable. I have other queries in the methods but an answer here will fix them too.

Answer 1

First query:

SELECT mix_name, date
FROM table
ORDER BY mix_number DESC
LIMIT 1, 0

Second query:

SELECT mix_name, date
FROM table
ORDER BY mix_number DESC
LIMIT 1, 1

If you want all rows from the same query:

SELECT mix_name, date
FROM table
ORDER BY mix_number DESC

If you want to more precisely sort rows with the same mix_number:

SELECT mix_name, date
FROM table
ORDER BY mix_number DESC, date DESC

Answer 2

You need an ORDER BY clause.

select mix_name, date order by mix_number desc

Adding LIMIT 0, 1 will be your first query. LIMIT 1, 1 will be second. And so on

Answer 3

select mix_name, date 
FROM tableName
where mix_number = (SELECT MAX(mix_number) FROM tableName)
LIMIT 1

or

select mix_name, date 
FROM tableName
where mix_number = (SELECT MAX(mix_number) FROM tableName) - 1
LIMIT 1