R
R
roadtoexp2018-09-14 12:33:19
MySQL
roadtoexp, 2018-09-14 12:33:19

How to select data without repetitions?

There is a request:

SELECT 
                sign(coalesce(location_id, 0)) + sign(coalesce(division_id, -1)) + sign(coalesce(position_id, -2)) as score, keyword.* 
            FROM 
                keyword 
            WHERE 
                city_id = $cityId AND 
                (division_id = $divisionId OR division_id IS NULL) AND 
                (position_id = $positionId OR division_id IS NULL) AND 
                (location_id = $locationId OR location_id IS NULL) AND
                MATCH (keyword) AGAINST ('$keys') 
            GROUP BY 
                title 
            ORDER BY 
                score 
            DESC LIMIT 10

He returns me a record that does not match the conditions at all. This is how group by seems to work. How can I reject those records that match the conditions, have the same title and differ only in score

Answer the question

In order to leave comments, you need to log in

1 answer(s)
R
Ruslan., 2018-09-14
@LaRN

Try like this:

SELECT 
      sign(coalesce(location_id, 0)) + sign(coalesce(division_id, -1)) + sign(coalesce(position_id, -2)) as score, title
  FROM 
      keyword 
 WHERE 
      city_id = $cityId AND 
      (division_id = $divisionId OR division_id IS NULL) AND 
      (position_id = $positionId OR division_id IS NULL) AND 
      (location_id = $locationId OR location_id IS NULL) AND
      MATCH (keyword) AGAINST ('$keys') 
GROUP BY 
      title 
ORDER BY 
      score 
DESC LIMIT 10

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question