Oracle solution to both problems:
SELECT ID, Category FROM (
SELECT FIRST_VALUE(ID) OVER
(PARTITION BY Category ORDER BY Ord) IDOfSmallestOrdForCategory
, Category, ID FROM DATAS
)
WHERE ID = IDOfSmallestOrdForCategory;
Generic solution to both problems:
SELECT MIN(a.ID), a.Category FROM DATAS a
JOIN (SELECT CATEGORY, COALESCE(MIN(ORD),0) MINORD FROM DATAS GROUP BY CATEGORY) b
ON COALESCE(a.ORD,0) = b.MINORD AND a.Category = b.Category
GROUP BY a.Category;
Oracle DDL/DML:
CREATE TABLE DATAS (
ID Integer,
CATEGORY Integer,
ORD Integer
);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (6, 3, NULL);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (7, 3, NULL);
If it is safe to assume that a single order can have only one distinct StoreID
, you could resolve your issue by generating the set of distinct OrderID, StoreID
pairs from orderLines
and join that set instead of the table itself. That way you will not need to use DISTINCT with aggregation:
SELECT
`ol`.`storeID`,
SUM(`o`.`grossValue`) AS 'Total',
SUM(`o`.`paymentValue`) AS 'paymentTotal'
FROM
`orders` AS `o`
LEFT JOIN
(SELECT DISTINCT `OrderID`, `StoreID` FROM `orderLines`) AS `ol`
ON `o`.`orderID` = `ol`.`orderID`
WHERE
(`o`.`orderDate` BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP
BY `ol`.`storeID`
;
And, as has already been mentioned, you should probably use INNER JOIN instead of the LEFT JOIN, unless you have orders that do not have order lines but for some reason do have a gross value and a payment (a little strange, but may be there is a reason for that), and you want them included in the results. (They will be represented as a single row with a null Store ID.)
Best Answer
You can use
ORDER BY
insideGROUP_CONCAT()
function. You can also change the separator, if you don't want to use comma. Further details in MySQL documentation:GROUP_CONCAT()
Example: