Solved

Need help with query

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Data Field - SQL 11 37
mysql query for sum() 3 28
Import MySQL data into MS Access using VB.Net interface 5 29
add criteria to query in VB, Access 2003 2 10
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Creating and Managing Databases with phpMyAdmin in cPanel.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

860 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