Simple Query

[sonia.sardana]

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

[ck9663]

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

[vijaybandi]

select distinct sname from student
union all
select distinct sname from student
union all
select distinct sname from student

[sonia.sardana]

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.

[debasisdas]

Kindly post the query that you are working on . Before asking for code you need to post the code that you have tried.

[sonia.sardana]

Sorry Sir, I have no idea abt this query,so what i post.

[debasisdas]

this will work

[code=oracle]
select distinct sname from student
union all
select distinct sname from student
union all
select distinct sname from student;
[/code]

[sonia.sardana]

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.

[siva538]

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 !

[sonia.sardana]

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.

[deric]

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.