CODEWITHSUNDEEP
phpmysql
Nick
Nick

Reputation: 906

MySql select by score desc - when there are multiple score totals.... ODD

ok, so I have a table filled with client "testimonials". Some are marked out of 10, and others are out of 5. (they come from different sources)

My table has the following fields:

id (int 4 ) AI Pri Index
headline varchar 255
content text
source varchar 55
score double

So my problem is this:

SELECT * from testimonials WHERE score > 8 ORDER BY rand()

this works perfectly for my main set of testimonials scored out of 10, but now I have a new set that are marked out of 5, I need to devise a new way to pull these out too and put them in the mix.

So far I have removed the score > 8 clause, and added a SWITCH limit clause in my script to remove <8 for source 1, and < 3 for source2.

These are now shown in order DESC, but I want the whole lot to be randomized, or at the least source1 and source2 all mixed together in the result.

I would prefer to do all this in the Query... Any ideas?

Upvotes: 3

Views: 68

Answers (4)

Jacques Amar
Jacques Amar

Reputation: 1833

You need to separate by source. Either inline for known sources

SELECT score, 
CASE source
   WHEN 1 THEN 10
   WHEN 2 THEN 5 ...
END CASE as max_score
FROM testimonials
WHERE (score/max_score*10) > 8
ORDER BY rand()

Or create a source table and join them

SELECT t.score, 
s.max_score
FROM testimonials t
LEFT JOIN source_table s
  ON s.source=t.source
WHERE (t.score/s.max_score*10) > 8
ORDER BY rand()

Upvotes: 0

Joel H.
Joel H.

Reputation: 690

You can just add a simple OR condition to your working query:

SELECT * from testimonials WHERE score > 8 OR (source = 'source2' AND score > 3) ORDER BY rand()

This selects scores > 8 for source1 and > 3 for source2 as your SWITCH explanation suggests.

Upvotes: 1

ishegg
ishegg

Reputation: 9947

How about normalizing on the spot according to source? Then you don't have to add another field:

SELECT source, score FROM testimonial WHERE (score/IF(source='1',10,5)) >= .8;

Fiddle

This assumes that source can be either 1 or 2.

Upvotes: 1

Ross
Ross

Reputation: 1679

Add a column with the value of a rating max - called max_score below. So in your case its either 5 or 10. Then normalize using a percentage:

 SELECT * from testimonials WHERE (score/max_score) > 0.8 ORDER BY rand()

Easy.

Upvotes: 4

Related Questions