similar titles

lajkonik86

DISTINCT aint cutting it this time

I need to do DISTINCT with some sort of margin

For example:
Battlefield 2 Trailer
Battlefield 2 Movie
stuff like this (note the beginning doesn't have to be the same)
It aint a problem if it excludes things which aren't the same, as long as it doesn't include things which are the same.

So is there any way i can check for similarity??
Preferably in the mysql query but yeay i doubt that's possible

LoganK

I'm not sure how in SQL you'd do the "negate" of a certain function (in PHP, it's "!", as in, if (!isset())), but it'd be the negate of "LIKE".

"SELECT FROM table WHERE name LIKE '%Battlefield'" will get you all results with the name containing "Battlefield", either in the beginning or end ("%" is the wildcard character). Find out how to negate it, and you've got yourself a query! ("SELECT FROM table WHERE name !LIKE '%Battlefield%'" ????)

Or you can do that, find out all the IDs of those rows, and make another query selecting anything BUT those IDs. But that'd be too over-the-top!

hitman6003

NOT is the SQL equavalent of the php ! I think, so your query would be:

SELECT * FROM table WHERE name NOT LIKE '%Battlefield%'

LoganK

Thanks!

lajkonik86

didn't know like 🙂 thx

Still i'm trying to get 5 distinct entries out of a database of around 50.
So in order to make that work with the above code. I should read the first value, then exclude it's first word, find the second value, exclude both values... i'd be quering for quite a bit.

mrhappiness

SELECT DISTINCT SUBSTRING_INDEX(columnname, ' ', 1) first_word
FROM tablename
LIMIT 5

hth

http://dev.mysql.com/doc/mysql/en/string-functions.html

LoganK

Thanks once again to mrhappiness. I'm sure his is faster then all my sub-queries!

lajkonik86

lots of thanks

just a little other question
I have a database with fields for the site and the position of an item
i would like to query for a distinct combination of the both.
So is there something like
distinct combination site, position

LoganK

I think it's "DISTINCT 'table' FROM 'site','position'".

Basically, same query but with both table names for the "FROM".

lajkonik86

I couldn't figure out how to select the distinct substring and also select the name and link, without quering a distinct combination. To circumvent this problem i used group by instead of distinct. Gives the same result 🙂

Code i used:

SELECT SUBSTRING_INDEX( name, ' ', 1 ) AS first_word, name, link
FROM `dandt`
WHERE `type` =1
GROUP BY first_word
ORDER BY `position`
LIMIT 8

LoganK

Allright then. Does this mean your problem is resolved? If so, you know what I'm gonna say... 😃