Give preference to greater equality, mysql

Asked

Viewed 282 times

1

I have a table with a column called X in it I enter values separated by comma, for example: 1,2,3,4,5.

Use this query to capture certain Row.

SELECT * FROM 'x' WHERE CONCAT(',',x,',') LIKE '%,1,2,%'

I wanted to adapt this query to give preference to Rows that have less difference. for example, I have Rows this way:

1,2,3,4,5,6,8,9[..]

1,2,3,8,91

3,4,5,1

1,2

When I play the query it shows me all the Rows, why they all contain the 1,2 However I wanted him to give preference to Rows whose difference is less or he would show me in that order:

1,2,3,4,5,6,8,9[.. ] - Third

1,2,3,8,91 - Second

3,4,5,1 - Room

1,2 - First

mysql sql

  • There may be some trick to solving the problem, but I believe you should use a relationship table instead of recording a list of numbers as a string.

  • what do you mean by CONCAT(',',x,',') in the WHERE?

  • 3,4,5,1 should not even come in the result !? I also did not understand such "difference" as said @bfavaretto normalize the table so come to facilitate things.

  • 1

    @Matthew This is already a trick to select lines that contain a certain number in that list.

  • informing the column itself in CONCAT? In the FROM he put the table 'x' and in the WHERE has the spine x, are different things?

  • In the WHERE the x from it means any number (think) @Matthew.

  • @bfavaretto I can not relate as these can be entered randomly.

  • @Matthew are separate things, but they have the same name.

  • @user3163662, I tried to answer as I understood, it may not really be the ideal solution, but it is an alternative, since you said you would not be able to do by relationship and the information we have are few to know if it has how or not!

Show 4 more comments

1 answer

1

Note: depending on the amount of records and the size of the column, probably the performance with the query won’t be good.

One way to show the smallest difference is by considering the number of characters of the returned lines, the smaller the number of carecters, the smaller the difference of the searched term. To do this check you can use the function CHAR_LENGTH as the example below:

select 
  [SuasColunas]
from 
  [NomeDaTabela] 
where 
  [NomeDaColuna] like '%1,2%' 
order by 
  char_length([NomeDaColuna])

You can see running on SQL Fiddle.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.