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.
| 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]
|
 |
|
|
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 |
|
|
|
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]
|
|
|
|
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) |
|
|
|
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]
|
|
|
|
|
|
|
How to create select statement and Split Data