CODEWITHSUNDEEP
mysqlsqldatabasewordpress
wp student
wp student

Reputation: 765

database query to get lowest price based on last crawel date

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');
}

enter image description here

Upvotes: 2

Views: 258

Answers (3)

Daniele Murer
Daniele Murer

Reputation: 247

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;

Upvotes: 2

cdaiga
cdaiga

Reputation: 4939

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;

Upvotes: 0

Poker Gaff PokerGaff_com
Poker Gaff PokerGaff_com

Reputation: 7

Maybe use ORDER BY crawled and LIMIT 1

Upvotes: -1

Related Questions