SQL/SQlite: Adding a column in a select statement based on a value of another column

I have a table with the columns word, length, position, count and definition. I want to make a select of word and definition but just keeping the definition of some words (based on a condition over position, count and length) and the rest of definitions replace them by an empty string.

I'm doi开发者_运维问答ng the following query:

SELECT word,definition FROM (SELECT word, definition, position, count FROM words 
WHERE position = 5000 AND count < 5 AND length <= 6
AND definition IS NOT NULL
UNION ALL
SELECT word, "" AS definition, position, count FROM words)
ORDER BY position ASC, count DESC

But I end up with duplicated values in the column word.

How can I get this without duplicated values?

Add the inverse WHERE clause to your second set?

SELECT word,definition FROM
(
  SELECT word, definition, position, count FROM words 
  WHERE position = 5000 AND count < 5 AND length <= 6
  AND definition IS NOT NULL
UNION ALL
  SELECT word, "" AS definition, position, count FROM words
  WHERE NOT (position = 5000 AND count < 5 AND length <= 6
             AND definition IS NOT NULL)
)
ORDER BY position ASC, count DESC

A SELECT DISTINCT seems to solve your problem.

http://www.w3schools.com/sql/sql_distinct.asp

You could use group by clause....

Use of UNION SELECT seems to be useless in your case. It seems that you want only to initialize definition column when it is NULL. Definition is only part of your outputs.

Use a ifnull to initialize definition :

SELECT word, IFNULL(definition,"") FROM words 
  WHERE position = 5000 AND count < 5 AND length <= 6
ORDER BY position ASC, count DESC

IFNULL will return definition is definition is not NULL and "" otherwise.

SQLITE ISNULL Documentation http://www.sqlite.org/lang_corefunc.html

MYSQL IsNULL Documentation: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull