Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 SQL Server 6.5 \ SQL Server 7.0
 How to create select statement and Split Data

Author  Topic 

kjubnoi
Starting Member

3 Posts

Posted - 2013-06-26 : 01:23:39
I have output data xx rows from 1 Statement. I want to Split output Data from 1 Statement to 20%, 30%, 50% of xx rows

i have data :
pd_id values
1001 115
1002 65
1003 55
1008 43
1010 33
1011 21
1013 20
1025 12
1033 11
1035 10

i want to show rank and seq follow below :
pd_id values rank seq
1001 115 A 1
1002 65 A 2
1003 55 B 1
1008 43 B 2
1010 33 B 3
1011 21 C 1
1013 20 C 2
1025 12 C 3
1033 11 C 4
1035 10 C 5

pls, help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-26 : 01:52:32
Are you really using SQL Server 6.5 / 7 ? It is much easier if you are using SQL 2005 or later


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

kjubnoi
Starting Member

3 Posts

Posted - 2013-06-26 : 03:19:58
thank for read my forums, I use sql server 7 because this place use only sql server 7
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-26 : 04:13:03
create a temp table with column rank char(1), output the result of your existing query to a temp table


-- update the rank in temp table
update t
set rank = case when rn * 100.0 / (select count(*) from #temp) <= 20
then 'A'
when rn * 100.0 / (select count(*) from #temp) <= 50 -- 20 + 30 = 50
then 'B'
else 'C'
end
from
(
select *, rn = (select count(*) from #temp x where x.pd_id <= t.pd_id)
from #temp t
) t

-- the final query
select *,
seq = (select count(*) from #temp x where x.rank = t.rank and x.pd_id <= t.pd_id)
from #temp t



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

kjubnoi
Starting Member

3 Posts

Posted - 2013-06-27 : 03:03:01
thank very much, khtan ^^ and i have a question ... Can we create select statement by use only 1 statement ??? (not use temp table or update)
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-27 : 20:48:01
don't think it is possible with SQL Server 7


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -