SELECT TOP (CALCULATION) - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT TOP (CALCULATION)

Author

Topic

johnnybutler7
Starting Member

16 Posts

Posted - 2008-11-11 : 06:58:17

Hi,

I want to replace the value that goes in top with the count of the total (records * 30 / 100)

So ive tried something like

SELECT TOP (count(*) / 30 * 100) news.id
FROM news
WHERE news.announced_date >= '2006-03-01' AND
news.announced_date <= '2006-03-31'

The above doesnt work.

An issue i will have is i will also need to round down or up depending on if the calculation is .5 or greater for up and .5 or less for lower.

So some sort of a case statement to check this.

Can anyone help, im a bit stuck as im new to these type of programming inside SQL queries.

JB

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-11 : 07:08:28

Your description doesn't make sense.

COUNT(*) / 30 * 100

will only product 1 value with the query you posted.

I think you are probably wanting to GROUP BY some criteria and then return the highest value of the groups?

Please post some sample data and expected results -- I think we would be able to give you a query very quickly.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 07:31:30

SELECT TOP (round(count(*) / 30.0 * 100,0)) news.id
FROM news
WHERE news.announced_date >= '2006-03-01' AND
news.announced_date <= '2006-03-31'

Madhivanan

Failing to plan is Planning to fail

johnnybutler7
Starting Member

16 Posts

Posted - 2008-11-11 : 07:35:12

i probably havent explained it pretty well.

If i have 11 news items for March 2006

id score
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11

What im trying to do is spike the data so i take 30% of the news items from the dataset follwoing these rules.

So 30% of 11 is 3.3 so i want to round that down to 3 and in this case i want to take 2 news items from the top and then 1 news item from the bottom.

If it was 4 i would tak 2 from the top and 2 from the bottom.

SELECT TOP (floor(count(news.id) / 30) * 100) / 2)) news.id, news.score
FROM news
WHERE news.announced_date >= '2006-03-01' AND
news.announced_date <= '2006-03-31'
order by score DESC

So the calculation after the SELECT TOP would be (floor((11 * 30) / 100) / 2)) which would return 3

expected results
1 1
10 10
11 11

JB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-11 : 08:46:35

SELECT TOP (@calc) is not allowed in SQL Server 2000.

E 12°55'05.63"
N 56°04'39.26"

johnnybutler7
Starting Member

16 Posts

Posted - 2008-11-11 : 08:58:44

Im on 2005, sorry. Ive moved this to the2005 forum

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114238

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources