Grab row(s) with highest value in specific colum

Question

I've got a simple table that is storing unique IDs tied to a download. What I'm trying to do is generate a CSV of the most recent IDs created. Each time keys are generated (anywhere from 1 to 100 keys at a time), a UNIX timestamp is stored with those keys.

I have the CSV file generating just fine, but I can't get the MAX function to work properly. My report generator is below:

// output headers so that the file is downloaded rather than displayed
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=codes.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings
fputcsv($output, array('Unique Codes'));

// fetch the data
/** database username and pass removed **/
$rows = mysql_query('SELECT uniqueid FROM downloadkeys');

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);

simshaun · Answer 1 · 2011-12-01T19:04:27.047

2

Something like this should work. I'm making assumptions about column names, so you will need to likely change the code to match your schema.

// fetch the data
/** database username and pass removed **/
$rows = mysql_query('SELECT uniqueid FROM downloadkeys ORDER BY timestamp DESC LIMIT 10');

Edit (untested):

// fetch the data
/** database username and pass removed **/
$rows = mysql_query('SELECT uniqueid, timestamp FROM downloadkeys ORDER BY timestamp DESC LIMIT 100');

$prev_timestamp = NULL;
$uniqueids = array();

while ($row = mysql_fetch_assoc($rows)) {
    if ($prev_timestamp !== NULL && $row['timestamp'] != $prev_timestamp)
        break;

    $uniqueids[] = array('uniqueid' => $row['uniqueid']);
    $prev_timestamp = $row['timestamp'];
}

foreach ($uniqueids AS $row)
    fputcsv($output, $row);

edited Dec 01 '11 at 19:04

answered Dec 01 '11 at 18:41

simshaun

21,263
1
57
73

I can't use LIMIT, because the most recent timestamp may have 1 row or 100. – Norcross Dec 01 '11 at 18:53
Oh. That will be more difficult then. See http://stackoverflow.com/questions/2643314/mysql-group-by-limit for a SQL-based example. Alternatively, if 100 is the most that will ever be generated, you could `LIMIT BY 100` and use PHP to filter out timestamps that don't belong. – simshaun Dec 01 '11 at 18:59
Updated my answer with an example (untested) that uses PHP. – simshaun Dec 01 '11 at 19:04

score 1 · Answer 2 · answered Dec 01 '11 at 18:42

1

SELECT id FROM table ORDER BY timestamp DESC LIMIT 5

You need to ORDER BY timestamps descending to get the latest. LIMIT X will return only X number of records.

answered Dec 01 '11 at 18:42

Kevin Peno

9,107
1
33
56

goat · Accepted Answer · 2011-12-01T19:20:30.063

1

select uniqueid 
  from downloadkeys
 where creationTime = ( select max(creationTime) 
                          from downloadkeys )

Or if you prefer join

select uniqueid 
  from downloadkeys
 inner
  join ( select max(creationTime) maxCreationTime
           from downloadkeys ) dk
    on creationTime = dk.maxCreationTime

edited Dec 01 '11 at 19:20

answered Dec 01 '11 at 19:12

goat

31,486
7
73
96

score 0 · Answer 4 · answered Dec 01 '11 at 18:59

0

… or, possibly:

SELECT uniqueid FROM downloadkeys WHERE timestamp=MAX(timestamp)

answered Dec 01 '11 at 18:59

BRPocock

13,638
3
31
50

1

`WHERE timestamp=(SELECT MAX(timestamp) FROM downloadkeys)` might be necessary? I'm afraid I haven't a chance to test this on a real MySQL :-( – BRPocock Dec 01 '11 at 21:09
1

…or, more generally: save the last timestamp that you'd dumped, and then `WHERE timestamp >` *previous-timestamp* – BRPocock Dec 01 '11 at 21:14

Grab row(s) with highest value in specific colum

4 Answers4