?
Solved

SQL Quiz

Posted on 2003-03-20
9
Medium Priority
?
583 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 69

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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

765 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