?
Solved

Need help with query

Posted on 2008-10-26
3
Medium Priority
?
226 Views
Last Modified: 2010-03-20
Hi,

My SQL query will return several times the same rows.
But if I have the same rows, I want only the row that has the highest value for 'relevance' field.
I thought maybe it is possible with a group by / having ?

I need some help with this,
Here is my PHP/MYSQL Code:

"SELECT " . $type . " as type, " .
"R1.nom_fr as region_departure_fr, R1.nom_nl as region_departure_nl, " .
"P1.nom_fr as province_departure_fr, P1.nom_nl as province_departure_nl, " .
"C1.nom_fr as commune_departure_fr, C1.nom_nl as commune_departure_nl, " .
"S1.nom_fr as section_departure_fr, S1.nom_nl as section_departure_nl, " .
"R2.nom_fr as region_arrival_fr, R2.nom_nl as region_arrival_nl, " .
"P2.nom_fr as province_arrival_fr, P2.nom_nl as province_arrival_nl, " .
"C2.nom_fr as commune_arrival_fr, C2.nom_nl as commune_arrival_nl, " .
"S2.nom_fr as section_arrival_fr, S2.nom_nl as section_arrival_nl, " .
($type === Constants::PASSAGER ? 'passengers' : 'seats') .
" as seats, departure, member_id, record_date, " .
($type === Constants::PASSAGER ? 'pets' : '0') . " as pets, relevance, P.id " .
"FROM (search_trajets S, " . ($type === Constants::PASSAGER ? 'passagers' : 'trajets') . " P " .
"INNER JOIN regions R1 ON R1.id = P.region_departure " .
"INNER JOIN regions R2 ON R2.id = P.region_arrival " .
"INNER JOIN communes C1 ON C1.id = P.commune_departure " .
"INNER JOIN communes C2 ON C2.id = P.commune_arrival) " .
"LEFT JOIN provinces P1 ON P1.id = P.province_departure " .
"LEFT JOIN provinces P2 ON P2.id = P.province_arrival " .
"LEFT JOIN sections S1 ON S1.id = P.section_departure " .
"LEFT JOIN sections S2 ON S2.id = P.section_arrival " .
"WHERE P.to_delete = 0 AND " .
($departSection != NULL && $arriveeSection != NULL ?
"(S.section_departure = '" . $departSection . "' " .
"AND S.section_arrival = '" . $arriveeSection . "' " .
"AND S.search = 'ss') OR " : '') .
($arriveeSection != NULL ?
"(S.commune_departure = '" . $departCommune . "' " .
"AND S.section_arrival = '" . $arriveeSection . "' " .
"AND S.search = 'cs') OR " : '') .
($departSection != NULL ?
"(S.section_departure = '" . $departSection . "' " .
"AND S.commune_arrival = '" . $arriveeCommune . "' " .
"AND S.search = 'sc') OR " : '') .
"(S.commune_departure = '" . $departCommune . "' " .
"AND S.commune_arrival = '" . $arriveeCommune . "' " .
"AND S.search = 'cc') OR " .
($arriveeSection != NULL ?
"(S.province_departure = '" . $departProvince . "' " .
"AND S.section_arrival = '" . $arriveeSection . "' " .
"AND S.search = 'ps') OR " : '') .
($departSection != NULL ?
"(S.section_departure = '" . $departSection . "' " .
"AND S.province_arrival = '" . $arriveeProvince . "' " .
"AND S.search = 'sp') OR " : '') .
"(S.province_departure = '" . $departProvince . "' " .
"AND S.commune_arrival = '" . $arriveeCommune . "' " .
"AND S.search = 'pc') OR " .
"(S.commune_departure = '" . $departCommune . "' " .
"AND S.province_arrival = '" . $arriveeProvince . "' " .
"AND S.search = 'cp') OR " .
"(S.province_departure = '" . $departProvince . "' " .
"AND S.province_arrival = '" . $arriveeProvince . "' " .
"AND S.search = 'pp') " .
"AND P.id = S." . ($type === Constants::PASSAGER ? 'passager_id' : 'trajet_id');

For example I'll display something like:
      [...]
      Knokke-Heist (Knokke)      Rouvroy (Rouvroy)      04h10      3
      Knokke-Heist (Knokke)      Rouvroy (Rouvroy)      04h10      3
      Knokke-Heist (Knokke)      Rouvroy (Rouvroy)      04h10      3
      Knokke-Heist (Knokke)      Rouvroy (Rouvroy)      04h10      3
      [...]

But I need only the one row with the highest value for the field 'relevance'.

Thank you.
0
Comment
Question by:KouKi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 10

Expert Comment

by:calpurnia
ID: 22809425
Have you tried using SELECT DISTINCT?
0
 
LVL 9

Accepted Solution

by:
jamesgu earned 740 total points
ID: 22809474
say, the result you got is : col1 , col2, col3, relevance

so, what you can do this:

select  DISTINCT col1 , col2, col3, relevance
from (your_table) t
join (
select  col1 , col2, col3, max(relevance) from (your_table) t group by  col1 , col2, col3
) tt
on t.col1 = tt.col1
and t.col2 = tt.col2
and t.col3 = tt.col3

0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question