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