MySQL Forums
Forum List  »  Newbie

How to get the latest added price?
Posted by: Jassim Rahma
Date: October 08, 2022 10:51AM

Hi,

I have a database where multiple rows are there representing daily prices for the item and I want to list the items by branches but to show the latest added price only.

I tried below SELECT but did not work:

SELECT MAX(prices.price_id) AS max_id, prices.price_id, prices.branch_id, prices.item_id, prices.item_price
FROM prices
JOIN items ON items.item_id = prices.item_id
JOIN branches ON branches.branch_id = prices.branch_id
GROUP BY prices.price_id, prices.item_id, prices.branch_id
ORDER BY prices.price_id DESC;



Here are my tables:

/*
Navicat MySQL Data Transfer

Source Server : Softnames
Source Server Type : MySQL
Source Server Version : 50729
Source Host : mysql.softnames.com:3306
Source Schema : goprice

Target Server Type : MySQL
Target Server Version : 50729
File Encoding : 65001

Date: 08/10/2022 20:49:19
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for prices
-- ----------------------------
DROP TABLE IF EXISTS `prices`;
CREATE TABLE `prices` (
`price_id` bigint(20) NOT NULL AUTO_INCREMENT,
`price_uuid` varchar(0) DEFAULT NULL,
`item_id` bigint(20) DEFAULT NULL,
`branch_id` bigint(20) DEFAULT NULL,
`currency_code` char(2) DEFAULT NULL,
`item_price` decimal(11,2) DEFAULT NULL,
`created_date` datetime DEFAULT NULL,
`created_user` bigint(20) DEFAULT NULL,
`created_ip` varchar(45) DEFAULT NULL,
PRIMARY KEY (`price_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of prices
-- ----------------------------
BEGIN;
INSERT INTO `prices` VALUES (8, '', 63, 3, NULL, 1.11, '2022-10-07 14:35:03', 111, '86.98.69.103');
INSERT INTO `prices` VALUES (9, '', 62, 7, NULL, 2.22, '2022-10-07 14:39:21', 111, '86.98.69.103');
INSERT INTO `prices` VALUES (10, '', 63, 7, NULL, 3.33, '2022-10-08 10:03:12', NULL, NULL);
INSERT INTO `prices` VALUES (11, '', 62, 7, NULL, 4.44, '2022-10-08 10:03:55', NULL, NULL);
COMMIT;

-- ----------------------------
-- Triggers structure for table prices
-- ----------------------------
DROP TRIGGER IF EXISTS `tg_prices_created_date`;
delimiter ;;
CREATE TRIGGER `tg_prices_created_date` BEFORE INSERT ON `prices` FOR EACH ROW BEGIN
SET NEW.created_date = UTC_TIMESTAMP();
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table prices
-- ----------------------------
DROP TRIGGER IF EXISTS `tg_prices_price_uuid`;
delimiter ;;
CREATE TRIGGER `tg_prices_price_uuid` BEFORE INSERT ON `prices` FOR EACH ROW BEGIN
SET NEW.price_uuid = UUID();
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;





/*
Navicat MySQL Data Transfer

Source Server : Softnames
Source Server Type : MySQL
Source Server Version : 50729
Source Host : mysql.softnames.com:3306
Source Schema : goprice

Target Server Type : MySQL
Target Server Version : 50729
File Encoding : 65001

Date: 08/10/2022 20:50:10
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for items
-- ----------------------------
DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
`item_id` bigint(20) NOT NULL AUTO_INCREMENT,
`item_uuid` varchar(255) DEFAULT NULL,
`item_name` varchar(45) DEFAULT NULL,
`item_type` varchar(255) DEFAULT NULL,
`barcode_format` varchar(255) DEFAULT NULL,
`item_barcode` varchar(255) DEFAULT NULL,
`item_category` int(11) DEFAULT NULL,
`is_active` bit(1) DEFAULT b'1',
`is_blocked` bit(1) DEFAULT b'0',
`created_date` datetime DEFAULT NULL,
`created_user` bigint(20) DEFAULT NULL,
`created_ip` varchar(255) DEFAULT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of items
-- ----------------------------
BEGIN;
INSERT INTO `items` VALUES (54, '1ba0367e-440a-11ed-bf4a-008cfa12d1e0', 'Whole Wheat Flour', 'Product', 'Ean13', '8901725121181', NULL, b'1', b'0', '2022-10-04 17:29:32', 999, '94.57.139.127');
INSERT INTO `items` VALUES (55, 'b6ce378f-440a-11ed-bf4a-008cfa12d1e0', 'P√Ętes Tortiglioni', 'Product', 'Ean13', '8076802085837', NULL, b'1', b'0', '2022-10-04 17:33:52', 999, '94.57.139.127');
INSERT INTO `items` VALUES (56, 'df50e461-440a-11ed-bf4a-008cfa12d1e0', 'Ferrero Nutella (630g)', 'Product', 'Ean13', '3017620428258', NULL, b'1', b'0', '2022-10-04 17:35:00', 999, '94.57.139.127');
INSERT INTO `items` VALUES (57, '51a68c83-440e-11ed-bf4a-008cfa12d1e0', NULL, 'Product', 'Upca', '012000058806', NULL, b'1', b'0', '2022-10-04 17:59:40', 999, '2.50.233.2');
INSERT INTO `items` VALUES (58, '7e6fc2f6-440e-11ed-bf4a-008cfa12d1e0', NULL, 'Product', 'Ean13', '8906086761588', NULL, b'1', b'0', '2022-10-04 18:00:55', 999, '2.50.233.2');
INSERT INTO `items` VALUES (59, '9bed1323-440e-11ed-bf4a-008cfa12d1e0', 'Lurpak Soft Unsalted', 'Product', 'Ean13', '5740900400924', NULL, b'1', b'0', '2022-10-04 18:01:45', 999, '2.50.233.2');
INSERT INTO `items` VALUES (60, 'f37730ae-44dd-11ed-bf4a-008cfa12d1e0', NULL, 'Product', 'Upca', '661812407564', NULL, b'1', b'0', '2022-10-05 18:45:58', 999, '2.50.233.2');
INSERT INTO `items` VALUES (61, '906702ec-44f9-11ed-bf4a-008cfa12d1e0', NULL, 'Product', 'Ean13', '9218202417422', NULL, b'1', b'0', '2022-10-05 22:03:37', 999, '2.50.233.2');
INSERT INTO `items` VALUES (62, '82a294ae-464f-11ed-bf4a-008cfa12d1e0', NULL, 'Product', 'Ean13', '8002270001352', NULL, b'1', b'0', '2022-10-07 14:51:22', 999, '86.98.69.103');
INSERT INTO `items` VALUES (63, 'a5eeb766-4681-11ed-bf4a-008cfa12d1e0', NULL, 'Product', 'Ean13', '5412036012226', NULL, b'1', b'0', '2022-10-07 20:50:16', 999, '2.50.233.2');
COMMIT;

-- ----------------------------
-- Triggers structure for table items
-- ----------------------------
DROP TRIGGER IF EXISTS `tg_items_item_uuid`;
delimiter ;;
CREATE TRIGGER `tg_items_item_uuid` BEFORE INSERT ON `items` FOR EACH ROW BEGIN
SET NEW.item_uuid = UUID();
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table items
-- ----------------------------
DROP TRIGGER IF EXISTS `tg_items_created_date`;
delimiter ;;
CREATE TRIGGER `tg_items_created_date` BEFORE INSERT ON `items` FOR EACH ROW BEGIN
SET NEW.created_date = UTC_TIMESTAMP();
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;





/*
Navicat MySQL Data Transfer

Source Server : Softnames
Source Server Type : MySQL
Source Server Version : 50729
Source Host : mysql.softnames.com:3306
Source Schema : goprice

Target Server Type : MySQL
Target Server Version : 50729
File Encoding : 65001

Date: 08/10/2022 20:51:13
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for branches
-- ----------------------------
DROP TABLE IF EXISTS `branches`;
CREATE TABLE `branches` (
`branch_id` bigint(20) NOT NULL AUTO_INCREMENT,
`branch_uuid` varchar(255) DEFAULT NULL,
`merchant_id` bigint(20) DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`branch_code` varchar(255) DEFAULT NULL,
`branch_name` varchar(255) DEFAULT NULL,
`branch_location` geometry NOT NULL,
PRIMARY KEY (`branch_id`),
SPATIAL KEY `idx_branch_location` (`branch_location`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of branches
-- ----------------------------
BEGIN;
INSERT INTO `branches` VALUES (2, 'ab232afa-4410-11ed-bf4a-008cfa12d1e0', 1, 1, NULL, 'Online', ST_GeomFromText('POINT(0 0)'));
INSERT INTO `branches` VALUES (3, 'ab232d66-4410-11ed-bf4a-008cfa12d1e0', 1, 2, NULL, 'Reem Mall', ST_GeomFromText('POINT(24.4882704 54.3824531)'));
INSERT INTO `branches` VALUES (6, 'ab232e1e-4410-11ed-bf4a-008cfa12d1e0', 7, 1, NULL, 'Online', ST_GeomFromText('POINT(0 0)'));
INSERT INTO `branches` VALUES (7, 'ab232ebf-4410-11ed-bf4a-008cfa12d1e0', 7, 2, NULL, 'Shams Boutik', ST_GeomFromText('POINT(24.4956737 54.4072875)'));
INSERT INTO `branches` VALUES (8, 'ab232f5e-4410-11ed-bf4a-008cfa12d1e0', 13, 2, NULL, 'The Gate District', ST_GeomFromText('POINT(24.4940126 54.4094498)'));
INSERT INTO `branches` VALUES (10, '9c1b7c4d-44f4-11ed-bf4a-008cfa12d1e0', 1, 2, NULL, 'Marina Mall', ST_GeomFromText('POINT(24.4754005 54.3220549)'));
COMMIT;

-- ----------------------------
-- Triggers structure for table branches
-- ----------------------------
DROP TRIGGER IF EXISTS `tg_branches_branch_uuid`;
delimiter ;;
CREATE TRIGGER `tg_branches_branch_uuid` BEFORE INSERT ON `branches` FOR EACH ROW BEGIN
SET NEW.branch_uuid = UUID();
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;

Options: ReplyQuote


Subject
Written By
Posted
How to get the latest added price?
October 08, 2022 10:51AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.