Results 1 to 4 of 4
May 18, 2011, 03:09 #1
MySQL Query - Order by best matches
I'm certainly no mySQL guru so i'm looking for some assistance with a query. The query below brings back 1 exact match fine but the data in the table is not fully complete. What I want to do is return the all results in order of 'all 5 match', '4 match', '3 match', '2 match', '1 match'.
SELECT * FROM master WHERE
I hope I've explained it ok! Any help would be appreciated. Thx in advance.
May 18, 2011, 04:54 #2
To get all results, get rid of the LIMIT
To order them based on the number of matching columns is a bit trickier I think. No time to think about it now, maybe someone else knows the solution?Guido - Community Team Leader
The 2013 SitePoint Awards - Nominate your heroes now!
Thinking Web: Voices of the Community
Blog - Free Flash Slideshow Widget
May 18, 2011, 09:26 #3
Thanks for the advice, I've gone down the multiple queries and PHP route for know but will look around to see if i can get it down to the 1 query.
May 18, 2011, 12:04 #4
- Join Date
- Jan 2004
- Uppsala, sverige
- 2 Post(s)
- 1 Thread(s)
select <columnsOfInterest>, case when make_id = $make then 1 else 0 end + case when model_id = $model then 1 else 0 end + case when variant_id = $variant then 1 else 0 end + case when engine_id = $engine then 1 else 0 end + case when issueyear_id = $issueyear then 1 else 0 end as matches from master where make_id = $make or model_id = $model or variant_id = $variant or engine_id = $engine or issueyear_id = $issueyear order by matches desc