Finding the Second Highest Value with SQL : DerekAllard.com

Finding the Second Highest Value with SQL

February 12^th, 2007

For a project I'm currently working on, subscribed users get up to the minute most current information (as outlined by a date field in MySQL). Unsubscribed users get the information still, only it is a day behind.

Due to the way the information is going into the system, there could be multiple entries for one date, and then no entries for a few days, then new, etc. Basically, the information cannot be predicted by the application. Retrieving the most recent date is trivially easy with a MAX command, but there is no "almostMAX" command, so I found myself staring at the screen wondering what the most efficient way to approach this was. Here's my solution:

SELECT * FROM table GROUP BY dateIssued ORDER BY dateIssued DESC LIMIT 1,1

Fortunately for me, LIMIT accepts offsets, and I can specific to grab only the second entry. If you can think of a more elegant way to do this (or heck, if you just want to comment), please leave a note below.

This entry was made on February 12^th, 2007 @ 9:10 and filed into How-To, PHP.

Comments

Shadowhand wrote on February 14^th, 2007 @ 8:39

Nice entry Derek. I don’t have any recommendations for you, however, I do have a comment: DON’T USE DATE FIELDS! They are silly and MySQL only. Use an unsigned int(11) field, and use Unix timestamps. It’s faster with large amounts of records and allows for finer-tuned searches by date. The date type is also not standard SQL, particularly MySQL’s date field, so it’s not compatible with other database types.

Derek wrote on February 14^th, 2007 @ 8:48

Great observation! I wish I had qualified my post now.

This project is one where I’ve inherited reams and reams of legacy data. Additionally, there are other tables that rely on the ones I’m using (wait, it gets worse) and the client has a data input system that uses Excel to upload information to MySQL.

I concede that I could port the whole thing over and update and… but the thought of it gives me headaches.

blackarma wrote on February 16^th, 2007 @ 4:33

If I have well understood your issue :

logged user get the last entry and non-logged users get the last entry delayed by a day.

You get the query for the logged users, for the other, just minus your date by one day and select the line where your timestamp is less or equal of the now() timestamp minus 24h order by timestamp desc limit 1 (do not forget to index your timestamp field).

Derek wrote on February 16^th, 2007 @ 4:56

Actually, I can’t just take a day away because there could be multiple entries for one date, and then no entries for a few days. If I just take away a day, it could be that they hit a day with no information, then the user gets a blank dataset… so that won’t work.

blackarma wrote on February 18^th, 2007 @ 21:36

Another solution is to get your max values for your timestamp field (must be indexed) and do a select to get the first entry after this max value :

select max(date_or_timestamp_value) from table 1 ;

select * from table 1 where date_or_timestamp_value < $value_query_one limit 1 ;

Andy Roberts wrote on February 20^th, 2007 @ 5:22

Pretty easy to find the second highest date in an MySQL table. The use of a subquery can bring you all the benefits.

Return second highest date:

SELECT

date < (SELECT MAX(date) from table)

Return entire row :

SELECT * FROM table where date = (



   SELECT                                           

     MAX(date)                                      

   FROM                                             

     table

   WHERE                                              

     date < (SELECT MAX(date) FROM table)

)

DerekAllard.com : CodeIgniter, ExpressionEngine, and the World of Web Design

Finding the Second Highest Value with SQL

Comments

Post a Comment