By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,341 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Simple Query

P: 95
i have a table student like
sname
-----
ram
ram
ram
raj
raj
raj

i need the output like
sname
-----
ram
raj
ram
raj
ram
raj

Its a interview question Asked in IBM
Apr 16 '08 #1
Share this Question
Share on Google+
10 Replies


ck9663
Expert 2.5K+
P: 2,878
i have a table student like
sname
-----
ram
ram
ram
raj
raj
raj

i need the output like
sname
-----
ram
raj
ram
raj
ram
raj

Its a interview question Asked in IBM
If there are no other fields, I might create a temporary table for this one.

--- CK
Apr 16 '08 #2

P: 1
select distinct sname from student
union all
select distinct sname from student
union all
select distinct sname from student
Apr 17 '08 #3

P: 95
hi Vijaybandi, coding u have given shows the following result-

raj
ram
raj
ram
raj
ram

But I want
ram
raj
ram
raj
ram
raj


Can u please explain ur Query,i m not getting it.

Suppose I Have the foll. table-
create table sonia(name varchar)
insert into sonia values('A')
insert into sonia values('A')
insert into sonia values('B')
select name from sonia

select distinct name from sonia
union all
select distinct name from sonia

In that case,this query shows result
A
B
A
B

How its possible since there are three records in the table.
Apr 17 '08 #4

debasisdas
Expert 5K+
P: 8,127
Kindly post the query that you are working on . Before asking for code you need to post the code that you have tried.
Apr 17 '08 #5

P: 95
Sorry Sir, I have no idea abt this query,so what i post.
Apr 17 '08 #6

debasisdas
Expert 5K+
P: 8,127
this will work

Expand|Select|Wrap|Line Numbers
  1. select distinct sname from student
  2. union all
  3. select distinct sname from student
  4. union all
  5. select distinct sname from student;
  6.  
Apr 17 '08 #7

P: 95
hi debasis, If a table contains the foll. records & we have to write the query to retrieve the records in the order A B A

create table sonia(name varchar)

insert into sonia values('A')
insert into sonia values('A')
insert into sonia values('B')

Then what is the query???

I have wtite the foll. query-
select distinct name from sonia
union all
select distinct name from sonia


It gives A B A B. Answer is wring cz there is only one record with value B.
Apr 19 '08 #8

P: 44
If I were to answer the question I would answer this way:

" there is nothing called order of rows in a set theory ! and so the rows placed in the table during it's insertion will be order of them by default. I would just delete the rows and insert in the required order and just do a select of all rows"

Better answers' welcome !
Apr 19 '08 #9

P: 95
Ya siva U R Right. Its simple to delete all rows and then reinsert the rows in the order u want. But it's a interview question & in interview we can't tell that answer-delete rows & then insertion.
Apr 19 '08 #10

P: 92
Ya siva U R Right. Its simple to delete all rows and then reinsert the rows in the order u want. But it's a interview question & in interview we can't tell that answer-delete rows & then insertion.
I guess there's really no perfect answer during interviews. For me, the important thing is that the interviewee has an idea/logic for the question and knows how to execute/implement his logic.

Anyway, if I would have to do this, I might assign numbers to the rows using select then select the resulting rows again with the use of the numbers by using Group By ASC or whatever. Or, I might use dynamic table...
Unfortunately, I don't have SQL Server to use here to try to code that... but at least I gave you some idea. I hope it could help.
Apr 21 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.