Solved

Need help with query

Posted on 2008-10-26
3
225 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 185 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

724 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