MySQL order group by

Question

Sorry in advance if this question has been asked before, but I can't seem to find any (good) answer on this.

I think this better explains my problem

  CREATE TABLE posts (
  id INT,
  name VARCHAR(10)

);

INSERT INTO posts VALUES
  (1, 'Product 1'),
  (2, 'Product 2');

CREATE TABLE details(
  id INT,
  merchant VARCHAR(10),
  address INT,
  lat decimal(11,8) ,
  lng decimal(11,8) ,
  offer_group INT(10)
) ;

INSERT INTO details VALUES
  (1, 'Merchant 1',10,40.444444,60.666666,1),
  (2, 'Merchant 1',20,40.555555,60.777777,1);

SELECT  P.*, D.*  , MIN( 3959 * acos( cos( radians('40.555556') ) * cos( radians( D.lat ) ) * cos( radians( D.lng ) - radians('60.777778') ) + sin( radians('40.555556') ) * sin( radians( D.lat ) ) ) ) AS distance  FROM posts P

  JOIN details D ON D.id = P.id 

  WHERE 1=1  GROUP BY merchant ORDER BY  distance ASC

http://sqlfiddle.com/#!9/7db78b/6 I have separate databases but the example link shows exactly how tables are linked and my current query.

So in that example if you leave the GROUP BY the distance is from the merchant with address 20 but I would like to see the correct data (id = 2, name = Product 2, address = 20, lat = 40.555555 , lng = 60.777777 )

Remove the GROUP BY part to see how it looks in the first place

Thanks and sorry for bad explainations

Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Paul Spiegel, May 10 '18 at 11:57
thanks, looked at the answer there and I tried the second approach but still have the same problem when grouping by merchant. http://sqlfiddle.com/#!9/7db78b/27 I am not that advance in understanding these queries, I might did it wrong. Any help would be appreciated . THanks — codernize, May 10 '18 at 16:48

Sebastian Brosch · Answer 1 · 2018-05-10T11:53:05.750

0

You can use the following:

 SELECT m1.* 
 FROM merchants m1 INNER JOIN (
     SELECT name, MIN(distance) AS distance FROM merchants GROUP BY name
 ) m2 ON m1.name = m2.name AND m1.distance = m2.distance

demo: http://sqlfiddle.com/#!9/6f9676/1/0

edited May 10 '18 at 11:53

answered May 10 '18 at 11:51

Sebastian Brosch

42,106
15
72
87

thanks, here's a more realistic version to what I have in code, could you work with this ? Thanks – codernize May 10 '18 at 16:50

score 0 · Answer 2 · answered May 10 '18 at 12:17

0

You can use the following query,

SELECT m1.* 
FROM merchants m1 
WHERE m1.distance = (SELECT MIN(distance) FROM merchants m2 where m1.name = m2.name);

answered May 10 '18 at 12:17

Abhilash Ravindran C K

1,818
2
13
22

MySQL order group by

2 Answers2