asked on

SQL Quiz

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

Guy Hengel [angelIII / a3]

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

JavaBoy060299

ASKER

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

Cheers

Scott Pletcher

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.

xenon_je

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

ASKER CERTIFIED SOLUTION

ispaleny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ispaleny

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 !

ispaleny

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).

JavaBoy060299

ASKER

Hi,

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

Thanks

ispaleny

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 !