Select statement

[sugiw]

Hi everyone:

In one of my interview, I got this question:

Table Actress
--------------
Actress_id First_name last_name
1 Britney Spears
2 Jennifer Lopez
3 Kelly Clarkson

I need a query to display these:

Britney Spears
Clarkson, Kelly
Jennifer Lopez
Kelly Clarkson
Lopez, Jennifer
Spears, Britney

In another word: I need to display all the names in two different formats: <first name> <last name> and <last name>, <first name> and sort them

I come up with this solution:
SELECT First_name, last_name from Actress
UNION
SELECT concat(last_name, ','), First_name from Actress
ORDER BY 1

But I was told that this query is not efficient, and there is a better way to do that.
Could anyone help?

Thanks so much -)

Sugi

 

[r937]

is there a better way to do it? no

the interviewer was probably just messin' wit ya

r937.com | rudy.ca

 

[piti]

maybe you were told, that your statement does not exactly do what has been requested ;-) - assuming the result should be only one column ...

 

[r937]

oh! oh! i just saw it! the inefficiency!!

i am such an idiot, it was staring me right in the face and i missed it )

the interviewer was right, there is a more efficient way

r937.com | rudy.ca

 

[sugiw]

piti: no, the interviewer did say that my way is inefficient, and there is a better way.
r937: really, how??

 

[r937]

UNION ALL



r937.com | rudy.ca

 

[sugiw]

r937: errr? why would that make the query more efficient?

 

[r937]

perhaps you don't know the difference between UNION and UNION ALL?

UNION removes duplicate rows

and since there cannot be any duplicate rows, that's unnecessary



r937.com | rudy.ca