0

Running MYSQL 5.5 and trying to essentially return only 1 record from each of the conditions in my IN clause. I can't use the DISTINCT because there should be multiple distinct records that are attached to each code (namely cost will be different) from the IN clause. Below is a dummy query of what I was trying to do, but doesn't work in 5.5 because of the ROW_NUMBER() function.

'1b' may have multiple records with differing cost values. title should always be the same across every record with the same codes value.

Any thoughts?

SELECT codes, name_place, title, cost
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY codes) rn
    FROM MyDB.MyTable
    )
WHERE codes IN ('1b', '1c', '1d', '1e')
AND rn = 1;
StormsEdge
  • 854
  • 2
  • 10
  • 35
  • 1
    You can simulate row number. See [here](https://stackoverflow.com/a/15623875) – Conrad Frix Jul 18 '19 at 14:23
  • Seams you are looking for PostgreSQL syntax `DISTINCT ON(codes), name_place, title, cost`, you can simulate/emulate it in MySQL/MariaDB with `SELECT SUBSTRING_INDEX(GROUP_CONCAT(...), ...) AS alias [,...] .. GROUP BY ..` see a [post off mine](https://stackoverflow.com/questions/53868870/how-to-use-distinct-on-with-mysql-using-activerecord/53869691#53869691) for example code.. Also @ConradFrix 's option is very valid and useable in MySQL 5.5.. – Raymond Nijland Jul 18 '19 at 14:27

0 Answers0