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 2005 Forums

Transact-SQL (2005)

SELECT TOP (@calculation)

Author

Topic

johnnybutler7
Starting Member

16 Posts

Posted - 2008-11-11 : 08:55:48

Hi,

I was wondering if anyone can help me,

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 (round(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

Can anyone help?

JB

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-11 : 09:02:34

You could put it in a variable , something like

declare @n int
set @n=3
select top (@n) * from Table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-11 : 09:09:32

[code]DECLARE @Sample TABLE
(
ID INT,
Score INT
)

INSERT @Sample
SELECT 1, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 3 UNION ALL
SELECT 4, 4 UNION ALL
SELECT 5, 5 UNION ALL
SELECT 6, 6 UNION ALL
SELECT 7, 7 UNION ALL
SELECT 8, 8 UNION ALL
SELECT 9, 9 UNION ALL
SELECT 10, 10 UNION ALL
SELECT 11, 11

DECLARE @Part FLOAT,
@Top INT,
@Bottom INT

SELECT @Part = 0.3, -- 30 percent
@Top = FLOOR(@Part * COUNT(*))
FROM @Sample

SELECT @Bottom = @Top / 2,
@Top = @Top - @Bottom

SELECT *
FROM (
SELECT TOP (@Top)
*
FROM @Sample
ORDER BY Score DESC
) AS d

UNION ALL

SELECT *
FROM (
SELECT TOP (@Bottom)
*
FROM @Sample
ORDER BY Score
) AS d[/code]

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

johnnybutler7
Starting Member

16 Posts

Posted - 2008-11-11 : 10:22:23

Thanks for the responses. Ive merged your example with my current query and come up with the below. The query is running but it returns every news item for that month(22) and the message print of the 2 variables @top and @bottom are,

(22 row(s) affected)
-----------------------
Side = 430
Perimeter = 429

Ive tried various different combinations but always get 22 news items but that is the total for that month that have scores and not the subset im looking for.

DECLARE @Part FLOAT,
@Top INT,
@Bottom INT

SELECT @Part = 0.3, -- 30 percent
@Top = FLOOR(@Part * COUNT(*))
FROM news

SELECT @Bottom = @Top / 2,
@Top = @Top - @Bottom

SELECT *
FROM (
SELECT TOP (@Top) id
FROM news
WHERE news.announced_date >= '2006-03-01' AND
news.announced_date <= '2006-03-31' AND news.score IS NOT NULL AND news.id IN (SELECT news.id FROM news
INNER JOIN news_targets ON news_targets.news_id = news.id
WHERE news_status_id <> 4)
ORDER BY news.score ASC
) top_news
UNION
SELECT *
FROM
(
SELECT TOP (@Bottom) id
FROM news
WHERE news.id IN (
SELECT DISTINCT news.id
FROM deals
WHERE news.announced_date >= '2006-03-01' AND
news.announced_date <= '2006-03-31' AND news.score IS NOT NULL AND news.id IN (SELECT news.id FROM news
INNER JOIN news_targets ON news_targets.news_id = news.id
WHERE news_status_id <> 4)
)
ORDER BY news_score DESC
) bottom_news

PRINT '-----------------------';
PRINT 'Side = ' + CONVERT(varchar(10), @Top, 10);
PRINT 'Perimeter = ' + CONVERT(varchar(10), @Bottom, 10);

Can you see anything wrong??

JB

johnnybutler7
Starting Member

16 Posts

Posted - 2008-11-11 : 10:46:33

My mistake, i just relaised i needed to add the conditions to the initial sql query

DECLARE @Part FLOAT,
@Top INT,
@Bottom INT

SELECT @Part = 0.3, -- 30 percent
@Top = FLOOR(@Part * COUNT(*))
FROM news
WHERE news.announced_date >= '2006-03-01' AND
news.announced_date <= '2006-03-31' AND news.score IS NOT NULL AND news.id IN (SELECT news.id FROM news
INNER JOIN news_targets ON news_targets.deal_id = news.id
WHERE news_status_id <> 4)

SELECT @Bottom = @Top / 2,
@Top = @Top - @Bottom

SELECT *
FROM (
SELECT TOP (@Top) id
FROM news
WHERE news.announced_date >= '2006-03-01' AND
news.announced_date <= '2006-03-31' AND news.score IS NOT NULL AND news.id IN (SELECT news.id FROM news
INNER JOIN news_targets ON news_targets.news_id = news.id
WHERE news_status_id <> 4)
ORDER BY news.score ASC
) top_news
UNION
SELECT *
FROM
(
SELECT TOP (@Bottom) id
FROM news
WHERE news.id IN (
SELECT DISTINCT news.id
FROM deals
WHERE news.announced_date >= '2006-03-01' AND
news.announced_date <= '2006-03-31' AND news.score IS NOT NULL AND news.id IN (SELECT news.id FROM news
INNER JOIN news_targets ON news_targets.news_id = news.id
WHERE news_status_id <> 4)
)
ORDER BY news_score DESC
) bottom_news

PRINT '-----------------------';
PRINT 'Side = ' + CONVERT(varchar(10), @Top, 10);
PRINT 'Perimeter = ' + CONVERT(varchar(10), @Bottom, 10);

This produces 6 record which im expecting 7 though 22 * 0.3 = 6.6 rounds to 7 so ive replaced Floor with round

thanks for your help guys

JB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-11 : 11:30:02

You can use CEILING as well.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources