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.

Could you clarify what you mean by desc rows? Are you saying you're trying to specifically query the rows with the top N mix_numbers? Is there a need to query them with separate SELECT statements? — JLRishe
– JLRishe, Commented Jan 10, 2013 at 0:40
Yes it was the top N mix_numbes I was going after, not row ID's — Brad
– Brad, Commented Jan 10, 2013 at 1:02

John Woo · Accepted Answer · 2013-01-10 00:26:53Z

1

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

answered Jan 10, 2013 at 0:26

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Brad Over a year ago

Thank you all, this is the one that worked the best for me. I appreciate all your answers and will keep them all in mind for future use. Thanks again

JLRishe Over a year ago

Note that this will only work if the mix_numbers are sequential and have no gaps. If you want the rows for the top N mix_numbers, you should just use SELECT mix_name, date FROM tableName ORDER BY mix_number DESC LIMIT N (replacing N with a number), and not do a bunch of separate queries.

Brad Over a year ago

Unfortunately the table is not normalized and you are right. One off number and Im screwed. There are about 10 songs in a row so I ahve to select a query for the tops mix mane and date, as well as all the songs per podcast for the bottom section

Robin Manoli Over a year ago

you can order by more than one column, for example: "ORDER by mix_number DESC, date DESC" to order by mix_number primarly, and by date if the mix_number is equal

Shiplu Mokaddim · Accepted Answer · 2013-01-10 00:37:26Z

1

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

answered Jan 10, 2013 at 0:37

Shiplu Mokaddim

57.8k20 gold badges147 silver badges193 bronze badges

2 Comments

Brad Over a year ago

If I use the limit when I go to query out the songs for the song query(one you didnt see) then I will only get the first, or second, or 3rd song. So the selectMAX was the best way for me to go

Robin Manoli Over a year ago

you may just remove "limit 0, 1" to get all rows

Robin Manoli · Accepted Answer · 2013-01-10 01:35:56Z

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

edited Jan 10, 2013 at 1:35

answered Jan 10, 2013 at 0:34

Robin Manoli

2,2222 gold badges27 silver badges31 bronze badges

Collectives™ on Stack Overflow

MYSQL Where function

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related