Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Need help with query

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
KouKi
Asked:
KouKi
1 Solution
 
calpurniaCommented:
Have you tried using SELECT DISTINCT?
0
 
jamesguCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now