?
Solved

SQL Quiz

Posted on 2003-03-20
9
Medium Priority
?
584 Views
Last Modified: 2006-11-17
Hi,

I have an SQL Quiz:

CREATE TABLE unknown
(
 field1 int,
 field2 int,
 field3 varchar(5),
 field4 varchar(5)
)

INSERT unknown values(2000,1,'A','X')
INSERT unknown values(2000,2,'B','X')
INSERT unknown values(2002,2,'C','Y')
INSERT unknown values(2003,1,'D','Y')
INSERT unknown values(2003,2,'E','Z')

expected result :
fieldx  fieldy
A         D
B         E
C

objective is how to put the record into two columns equally.

select top 50 percent field3 from unknown
select field3 from unknown where field3 not in (select top 50 percent field3 from unknown)

I want to create one sql statement, anyone know how to combine above statement into two column in one query?


thanks
0
Comment
Question by:JavaBoy060299
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8174655
I don't think you can do that without using temporary tables using identity fields:

create table #t1 ( f1 char(1), row int identity)
create table #t2 ( f2 char(1), row int identity)

insert into  #t1 (f1) select field3 from unknown where field3 in (select top 50 percent field3 from unknown)
insert into  #t2 (f2) select field3 from unknown where field3 not in (select top 50 percent field3 from unknown)

select f1, f2
from #t1
LEFT JOIN #t2
on #t1.row = #t2.row

drop table #t1
drop table #t2


CHeers
0
 

Author Comment

by:JavaBoy060299
ID: 8174702
Hi angellll,
i also think it is not possible.
but who knows someone has a very good idea.

Cheers
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 8174864
You might hit an unexpected problem with this code:

insert into  #t1 (f1) select field3 from unknown where field3 in (select top 50 percent field3 from unknown)
insert into  #t2 (f2) select field3 from unknown where field3 not in (select top 50 percent field3 from unknown)


Since there is no ORDER BY, there is no guarantee that the first "SELECT TOP 50 PERCENT" will be all the same rows as the second "SELECT TOP 50 PERCENT".  Admittedly it's very likely, but it is not GUARANTEED.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:xenon_je
ID: 8174883
A solution could be if you would have in your table an additional field, id, that you are sure has the values 1,2,3,....
As a solution,you can create another table that just adds this id, something like this

create table #t(
id int identity,
field1 int,
field2 int,
field3 varchar(5),
field4 varchar(5)
)

insert into #t
select * from unknown
--now get the result
declare @count int
select @count = count(*) from #t
select s1.field3 fieldx, isnull(s2.field3, '') fieldy
from #t s1,#t s2
where s1.id<= @count/2 and s2.id>1 + @count/2


xenon

0
 
LVL 13

Accepted Solution

by:
ispaleny earned 200 total points
ID: 8177260
select fieldx=min(x1.field3), fieldy=nullif(max(x1.field3),min(x1.field3))
from
(
 select t1.field3,Cnt=count('x')
 from unknown t1
 join unknown t2 on t1.field3>=t2.field3
 group by t1.field3
) x1
cross join (select HalfCnt=(count(*)+1)/2 from unknown) xx
group by (x1.Cnt)%xx.HalfCnt
order by fieldx

0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8177405
And this will be much faster for a large table
---------------------------------------------------

declare @HalfCount int
select field3,UID=identity(int,1,1)
into #unknown
from unknown
order by field3
set @HalfCount=(@@rowcount+1)/2
select fieldx=t1.field3, fieldy=t2.field3
from      #unknown t1
left join #unknown t2 on t1.UID=t2.UID-@HalfCount
where t1.UID<=@HalfCount
order by t1.UID
drop table #unknown

Good luck !
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8177496
The issue of the single query algorithm is number of records read.

Single query
-------------------- =
Fast query

(N^2)/2
-------------------- =
3*N

1/6*N
===============

But you want a single query (03/20/2003 02:19PM PST).
0
 

Author Comment

by:JavaBoy060299
ID: 8178200
Hi,

You are very creative person, very good idea
Yes,I knew that someone can do it !

Thanks
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8178381
JavaBoy,

Try this:

{DDL create table viData(DateCol datetime not null)}
Compute 3 year rolling averages of the count of records.

You can see answer at
http://dbforums.com/showthread.php?threadid=717155

BUT TRY TO SOLVE IT BY YOURSELF !!!

Good luck !
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question