order by

[Destramic]

hey guys i've made alterations to my query below, previously it had a lot of sub queries which was unnecessary as it can all be achieved by a join instead.

 

but this query brings back the wrong highest_bidder and highest_bidders_id which is brought from my bid and users table

 

if i do a simple query like this:

SELECT b.bid_id,
u.user_id, u.username, b.price
FROM bids b
LEFT JOIN users u ON u.user_id = b.user_id
ORDER BY b.price DESC,
         b.created_timestamp DESC,
        b.bid_id DESC
LIMIT 1

than i get the correct results as i should with this big query below.

SELECT i.item_id,
                         i.user_id,
				         i.title,
				         i.buy_now,
				         i.auction,
                         i.condition,
                         i.offers_accepted,
                         i.free_delivery,
                         i.free_delivery_condition,
                         i.collection,
                         i.collection_only,
		                 i.p_and_p,
                         i.buy_now_price,
                         i.quantity,
                         i.description,
                         (i.quantity - count(ip.item_id)) AS `quantity_available`,
                         COUNT(ip.item_id) AS `quantity_sold`,
                         io2.offer_price,
                         u.username AS `seller`,
                         DATE_FORMAT(u.timestamp, '%d/%m/%y') AS `member_since`,
                         @bid_increment := bi.increment AS `bid_increment`,
                         @current_auction_price := IF (b.price IS NULL, i.starting_price, b.price) AS `current_auction_price`,
                         TRUNCATE((@current_auction_price + @bid_increment), 2) AS `minimum_bid`,
                         ua.town_city,
                         ua.country,
                         ua.continent,
                         u2.username AS `highest_bidder`,
                         u2.user_id AS `highest_bidder_id`,
                         io2.quantity AS `quantity_offer`,
                         LOWER(c.country_code) AS `sellers_country_code`,
                         COUNT(DISTINCT io.item_offer_id) AS `offer_count`,
                         CONCAT(FORMAT(((SUM(uf.positive + uf.neutral) - uf.negative) / count(uf.user_feedback_id) * 100), 2), '%') AS `sellers_feedback_percentage`,
				         ROUND(SUM((uf.positive * 1) + (uf.neutral * 0.5)) - (uf.negative * 1), 0) AS `sellers_feedback_score`,
                         COUNT(DISTINCT b.bid_id) AS `bid_count`,
                         COUNT(DISTINCT uiw.user_item_wish_id) AS `wish_count`,
                         COUNT(DISTINCT uiwa.user_item_watch_id) AS `watch_count`,
                         CONCAT_WS(', ', ua.town_city, ua.county, ua.country) AS `location`,
                         NOW() AS `server_time`,
				         @timestamp := CONVERT_TZ(DATE_ADD(i.start_timestamp, INTERVAL concat(i.listing_duration) DAY), '+00:00', '+00:00') AS `timestamp`,
                         DATE_FORMAT(@timestamp, '%D, %M at %h:%i %p') AS `end_timestamp`,
                         (SELECT COUNT(reported_item_id)
                          FROM reported_items
                          WHERE item_id = i.item_id
                          AND user_id = :user_id) AS `reported`,
                         (SELECT COUNT(item_id)
                          FROM user_item_wishes 
                          WHERE item_id = i.item_id
                          AND user_id = :user_id) AS `wished`,
                         (SELECT COUNT(item_id) 
                          FROM user_item_watches 
                          WHERE item_id = i.item_id
                          AND user_id = :user_id) AS `watched`,
                         (SELECT COUNT(user_follower_id) 
                          FROM user_followers
                          WHERE user_id = i.user_id
                          AND follower_id = :user_id) AS `followed`,
                         (SELECT COUNT(DISTINCT(ip_address)) 
                          FROM statistics 
                          WHERE DATE_ADD(visited, INTERVAL 2 HOUR) >= now()) AS `period_last_visited`,
                         (SELECT COUNT(DISTINCT(ip_address)) 
                          FROM statistics 
                          WHERE uri = '/item/1') AS `views`,
                          IF (i.start_timestamp >= now(), true, false) AS `ended`,
                          CASE WHEN (:latitude IS NOT NULL AND 
				                     :longitude IS NOT NULL AND
				                     u.latitude IS NOT NULL AND
				                     u.longitude IS NOT NULL)
				               THEN 
                                    @distance := (SELECT (IF(:distance_unit = 'Kilometers', 6371, 3959) *
                                                  2 * ASIN(SQRT(POWER(SIN((:latitude- u.latitude) *
                                                  pi()/180 / 2), 2) + COS(:latitude * 
		                                          pi()/180) * COS(u.latitude * pi()/180) * 
		                                          POWER(SIN((:longitude - u.longitude) * 
		                                          pi()/180 / 2), 2)))))
				         END,
                         @distance_unit := IF (@distance >= 1, IF (@distance < 2, REPLACE (:distance_unit, 's', ''), @distance_unit), :distance_unit),
				         IF (@distance, CONCAT(TRUNCATE(@distance, 0), space(1) , @distance_unit), 'Unknown Distance') AS `distance`
				  FROM items i
				  LEFT JOIN users u ON u.user_id = i.user_id
                  LEFT JOIN item_purchases ip ON ip.item_id = i.item_id
                  LEFT JOIN user_item_wishes uiw ON uiw.item_id = i.item_id
                  LEFT JOIN user_item_watches uiwa ON uiwa.item_id = i.item_id
                  LEFT JOIN user_addresses ua ON ua.user_id = i.user_id
                  LEFT JOIN item_addresses ia ON ia.user_address_id = ua.user_address_id
                  LEFT JOIN countries c ON c.country_name = ua.country
                  LEFT JOIN item_offers io ON io.item_id = i.item_id
                  LEFT JOIN users_feedback uf ON uf.user_id = i.user_id
                  LEFT JOIN item_offers io2 ON io2.user_id = i.user_id
                  LEFT JOIN bids b ON b.item_id = i.item_id
                  LEFT JOIN users u2 ON u2.user_id = b.user_id
                  LEFT JOIN bid_increments bi ON b.price BETWEEN bi.price_from AND bi.price_to
                  WHERE i.item_id = :item_id
                  AND ia.user_address_id = i.user_address_id
                  AND uf.seller = '1'
                  ORDER BY b.price DESC,
                           b.created_timestamp DESC,
                           b.bid_id DESC

the joins and the order by are correct so i dont understand why the query is producing the wrong highest bidder if anyone know why please?

 

also any general advice on this query, how i should do things would be greatly welcomed.

 

thank you

 

[Destramic]

no views?

[Destramic]

with a lot of playing about i've narrowed it down to these columns causing the problem

(i.quantity - count(ip.item_id)) AS `quantity_available`,
COUNT(ip.item_id) AS `quantity_sold`,
io2.quantity AS `quantity_offer`,
COUNT(DISTINCT io.item_offer_id) AS `offer_count`,
CONCAT(FORMAT(((SUM(uf.positive + uf.neutral) - uf.negative) / count(uf.user_feedback_id) * 100), 2), '%') AS `sellers_feedback_percentage`,
ROUND(SUM((uf.positive * 1) + (uf.neutral * 0.5)) - (uf.negative * 1), 0) AS `sellers_feedback_score`,
COUNT(DISTINCT b.bid_id) AS `bid_count`,
COUNT(DISTINCT uiw.user_item_wish_id) AS `wish_count`,
COUNT(DISTINCT uiwa.user_item_watch_id) AS `watch_count`,

when these lines are taken away from my query i get the correct results...why when i add them the highest bidder changes?

 

thank you

[Barand]

Probably because you are using aggregation functions without a GROUP BY.

 

Does your query only produce a single row?

[Destramic]

yes its only is to produce one row by i.item_id

Edited September 6, 2015 by Destramic

[Barand]

Without a GROUP BY clause the aggregations will be for the whole table and non-aggregated fields will likely be whatever happened to be in those columns in the first record in the table.

[Destramic]

ok barand i'll give it a whirl...been busy reconstructing my database after all your advise...hopefully be able to play around with query again tomorrow