database query to get lowest price based on last crawel date

Question

I would like to get lowest price of product based on last crawled dates by various resellers. My current function is very basic, it gets me lowest price from table without considering reseller ids and crawled timestamps.

I've rough idea that we can SELECT * FROM "custom_data_table" and process the data using php. Please have a look at attachment for further clarification.

function get_lowest_price($table_id) {
    global $wpdb;
    $table_prices = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT price FROM `custom_data_table` WHERE tableid= %d"
            ,$table_id)
    );
    if (!empty($table_prices) && $table_prices !== NULL) 
        return rtrim(min($table_prices)->price, '00');
}

Duplicate? https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group?rq=1 — Paul Campbell, Mar 13 '18 at 15:29

Daniele Murer · Accepted Answer · 2018-03-15T08:58:32.483

2

The right query here is:

SELECT price
FROM custom_data_name cdn, (
     SELECT MAX(crawled) AS maxCrawled, resellerid
     FROM custom_data_name
     GROUP BY resellerid
) cdnFiltered
WHERE cdn.crawled = cdnFiltered.maxCrawled AND
cdn.resellerid = cdnFiltered.resellerid AND
tableid = %d;

edited Mar 15 '18 at 08:58

answered Mar 13 '18 at 15:04

Daniele Murer

247
3
9

score 0 · Answer 2 · answered Mar 13 '18 at 15:16

0

Try this:

SELECT B.price 
FROM (SELECT resellerid, MAX(crawled) max_crawled
      FROM custom_data_table
      GROUP BY resellerid) A
JOIN custom_data_table B 
ON A.resellerid=B.resellerid AND A.max_crawled=B.crawled;

answered Mar 13 '18 at 15:16

cdaiga

4,861
3
22
42

score -1 · Answer 3 · answered Mar 13 '18 at 15:00

-1

Maybe use ORDER BY crawled and LIMIT 1

answered Mar 13 '18 at 15:00

Poker Gaff PokerGaff_com

7

database query to get lowest price based on last crawel date

3 Answers3