Include a string in Select Query

Question

I'm wondering can we do this query below?

SELECT America, England, DISTINCT (country) FROM tb_country

which will (my intention is to) display :

America  
England  
(List of distinct country field in tb_country)

So the point is to display (for example) America and England even if the DISTINCT country field returns nothing. Basically I need this query to list a select dropdown, and give some sticky values user can pick, while allowing themselves to add a new country as they wish.

It also goes without saying, that should one row in the tb_country has a value of America or England, they will not show as a duplicate in the query result. So if the tb_country has list of values :

Germany  
England  
Holland

The query will only output :

America  
England  
Germany  
Holland

OMG Ponies · Accepted Answer · 2011-01-25 06:51:20Z

7

You need to use a UNION:

SELECT 'America' AS country
UNION
SELECT 'England' AS country
UNION 
SELECT DISTINCT(c.country) AS country
  FROM TB_COUNTRY c

UNION will remove duplicates; UNION ALL will not (but is faster for it).

The data type must match for each ordinal position in the SELECT clause. Meaning, if the first column in the first query were INT, the first column for all the unioned statements afterwards need to be INT as well or NULL.

answered Jan 25, 2011 at 6:51

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Henson Over a year ago

is there any other way? The query will be very long if I have to list 10-20 sticky values this way . If there's no other way, I'm gonna use your suggestion. Thanks

OMG Ponies Over a year ago

@Henson: Best option for this situation is to have a countries table that holds a unique list of countries. Then reference a primary key, like country_id wherever you need country information in other tables.

OMG Ponies Over a year ago

@Henson: Very weird - might want to present how much of a pain it will be to update, and perform vs a dedicated table.

VGE · Accepted Answer · 2011-01-25 07:04:06Z

0

Why you do not add a weight column in tb_country and use a order clause :

Perform once:

update country set weight = 1 where country = 'England';
update country set weight = 1 where country = 'America';

Then use it:

select distinct(country) from tb_country order by desc weight ;

Another way is to use an extra country table with two columns (country, weight) and an outer join.

Personnaly I rather prefer a country table with a UNIQUE constraint for country field and Use of a foreign key.

answered Jan 25, 2011 at 7:04

VGE

4,20120 silver badges18 bronze badges

Collectives™ on Stack Overflow

Include a string in Select Query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related