Keep only top 20

[EchoFool]

Hey,

 

Im having difficulty storing a top 20 chart based on most sales in descending order.

 

Lets say for simplicity the table was listed with 20 rows and the sales were 1 to 20 in perfect order.

 

But now along comes a new artist who sold 3.

 

So now there is 21 in total so the "1" has to drop off because thats the lowest and it can only show the top 20.

 

So it would then be 2,3,3 ....20.

 

How ever how can i apply this in php so it will always remain having 20 rows in the dabtase.. =/

[KevinM1]

ORDER BY sales DESC LIMIT 20

 

 

[EchoFool]

Thats my current method - but that doesn't solve the issue cos that means with the new artist i have 21 rows now but only 20 showing so i would need to replace one row some how to keep my database small.

[mikosiko]

How ever how can i apply this in php so it will always remain having 20 rows in the dabtase.. =/

 

at first glance my answer would be the same than Nightslyr's  but your quoted sentence specially the part that say "remain having 20 rows in the database" open the possibility of a different answer...

 

- if you really want to display the highest 20 records from all the existent in your table Nightslyr answer is correct.

- if you want to maintain in your table ONLY the 20 highest records that is a total different problem and solution

 

which one you want?.... EDITED:  just saw your answer

 

[mikosiko]

in that case you should :

- Select the record with MIN(sale)... compare it with your new record and proceed accordingly (NOTHING, DELETE/INSERT)

 

 

[EchoFool]

When you say compare it - what kind of comparison am i trying to do ?

[mikosiko]

amount sold for the new artist compared with the MIN(sold) that you retrieve from your current table

[KevinM1]

I guess I just don't see why you'd want to only keep 20 records in your database. 

 

For one, worrying about db size to that minute detail strikes me as a bit OCD.  Databases are supposed to hold data, and a mere 20 records is a very small sample size.  Also, if your HDD is really that tight, you have other problems to worry about.

 

Two, wouldn't the sales numbers of the least effective people be just as important as the most effective?  And wouldn't it make sense to track these numbers on a monthly basis?

 

To me, your request indicates a llack of time spent planning the database design.  Not trying to be overly harsh, but I think you're painting yourself in a corner, where it will be hard to modify and expand your project because you're locked into an inflexible db design.

 

Just something to think about, IMO.