Query GROUP BY

[Aman22]

Please advise me, to this following group function in PHP. this works perfectly fine,it groups the airline name and starts with the MIN price, however the problem is that it also shows the season but it is picking the MIN season which is not required. I want it to pick the season for the MIN price which it applies for in the same row.

 

the code is below...all help is much appreciated...thank you in advance:

 

$sql="SELECT MIN(price),dep,des,airline,flighttype,baggage,season,cabin,id FROM faresheet WHERE cabin = '".$q."' AND des = 'DEL' GROUP BY airline ORDER BY MIN(price) ASC";

[Pikachu2000]

Not a php question. Moving to MySQL help.

[Psycho]

The GROUP BY clause should just use 'price' not 'MIN(price)'. Not sure if that will fix your problem, but give it a shot.

 

EDIT: Never mind, that won't work. May need a subquery. I'll see if I can find a solution if no one else has an answer.

[Psycho]

OK, this "should" work, but I leave it to you to test. Also, not sure if this is the most efficient method. This basically puts the records in the correct order before doing the GROUP BY

 

$sql="SELECT *
      FROM (SELECT price, dep, des, airline, flighttype, baggage, season, cabin, id
            FROM faresheet
            WHERE cabin = '{$q}'
              AND des = 'DEL'
            ORDER BY price ASC) as temp
      GROUP BY airline";

[fenway]

You can't use GROUP BY with *.

[Psycho]

You can't use GROUP BY with *.

Sure you can. I tested the query above before I posted and I just ran a very simply query using

SELECT * FROM table_name GROUP BY field_name

which worked fine as well.

 

My results showed that when using GROUP BY on such a query the first record for the duplicate values in the GROUP BY clause are returned. So, the query I posted above creates a temporary table that orders the values such that the records returned are the ones the OP is after. I did state

. . . not sure if this is the most efficient method

 

But, it does work. I would be interested in knowing a more proper method to get the requested data.

 

 

EDIT: From the MySQL manual (emphasis added)

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL because the name column in the select list does not appear in the GROUP BY:

 

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

 

For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.

 

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL

[fenway]

Unless all of the other column depend on the group by column, the data you get is garbage.