Mysql query with full text search and offset when values are equal

240 Views Asked by At

I have this table called fruits

CREATE TABLE fruits (
  type varchar(10) NOT NULL,
  variety varchar(20) NOT NULL,
  price decimal(5,2) NOT NULL default 0,
  PRIMARY KEY  (type,variety)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE fruits ADD FULLTEXT (type, variety);

insert into fruits(type, variety, price) values
('apple',  'gala',       2.79),
('apple',  'fuji',       2.79),
('apple',  'limbertwig', 2.87),
('apple', 'valencia',   0.87),
('orange', 'navel',      9.36),
('pear',   'bradford',   6.05),
('pear',   'bartlett',   2.14),
('cherry', 'bing',       2.55),
('cherry', 'chelan',     6.33);

and I am doing a Select query with Full Text Search.

select *,
    MATCH (type, variety) AGAINST ('apple') as score
from fruits
WHERE MATCH (type, variety) AGAINST ('apple') > 0 AND price <= 2.79
order by score
LIMIT 2 

with this result

type  | variety | price | score 
-------------------------------
apple | gala    |  2.79 | 0.21812663972377777
apple | fuji    |  2.79 | 0.21812663972377777
apple | valencia | 0.87 | 0.21812663972377777

How can I Offset the result by 1 when

  • search score on row 1 = search score on row 2

  • price on row 1 = price on row 2

What I want from my query is to return

type  | variety | price | score 
-------------------------------
apple | fuji    |  2.79 | 0.21812663972377777
apple | valencia | 0.87 | 0.21812663972377777

since

  • price = 2.79 on rows 1 and 2

  • score = 0.21812663972377777 on rows 1 and 2

Thanks in advance for any potential help

0

There are 0 best solutions below