Need help with mysql query

Question

hi all, I'm new to mysql so please can you help me. I have 2 tables "words" and "text"

words has columns:

1. word
2. synonyms

text has columns:

1. text
2. word
3. article_id

I need to get unique words.word, and biggest unique text.atricle_id fields. The same article_id can have different words. for example

words 
word     synonyms
 ----------------------- 
 car      auto, vehicle 
 water    syn1, syn2      
 bus      syn1, syn2

 text 
 text          word        article_id
 --------------------------------------- 
 any text      car            1
 any_text      water          1
 any_text      water          2
 any_text      car            2
 any_text      bus            1
 any_text      bus            3

 I need to get the result: 
 car   | 2
 water | 1
 bus   | 3

I have a query but it return non unique article_id

SELECT words.word, text.article_id 
FROM `words` , `text`
WHERE text.word = words.word
GROUP BY words.word
ORDER BY text.article_id DESC

Answer 1

Maybe this will do it:

SELECT DISTINCT words.word, text.article_id 
FROM `words` , `text`
WHERE text.word = words.word
HAVING max(text.article_id)
GROUP BY words.word
ORDER BY text.article_id DESC

Answer 2

This query will get the result you want:

SELECT words.word, max(text.article_id) as biggest_article_id
FROM `words` , `text`
WHERE text.word = words.word
GROUP BY words.word
ORDER BY text.article_id DESC

Result:

 word  _ biggest_article_id
 bus   | 3
 car   | 2
 water | 2

Note 1: water has biggest_article_id = 2 and not = 1 as you state in the question.

Note 2: ORDER BY text.article_id DESC won't give you the result in the order you state in the question.