Select Top n - 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

Other Forums

MS Access

Select Top n

Author

Topic

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-24 : 07:22:57

Hi there,

Is there a way to get the 6 in the bottom code to be an input parameter?

OR

Is there a way to change the 6 to be replaced by the value of a field of a table?

SELECT Q.*
FROM ComptTeam AS Q
WHERE Q.uidLifter IN
(
  SELECT TOP 6 F.uidLifter
  FROM ComptTeam  F
  WHERE F.TeamName = Q.TeamName
  ORDER BY F.Points DESC, F.ScoreTotal DESC, F.uidLifter;
)
ORDER BY Q.TeamName, Q.Points DESC , Q.ScoreTotal DESC;

Regards.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-24 : 09:01:37

do a search for posts by my username and the word "rank" here at SQLTeam (all forums, not just Access), you should seen tons of posts about calculating the rank for each row in a table based on different columns for different groups of rows (i.e., in this case, a Rank per TeamName group it appears), how to handle ties, etc.

Once that you can generate that SQL, Rank is just a column in the recordset like any other and you can say WHERE [Rank] <= [some parameter].

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-24 : 16:45:32

TenTwenty,
pity... but the TOP "argument" must be hardcoded explicitly.

Lee-Z
Starting Member

7 Posts

Posted - 2005-01-31 : 07:12:03

Maybe using sp_ExecuteSql is an option in your case?

declare @SomeString nVarchar(4000)
declare @TopParam integer

set @TopParam = 6

set @SomeString =
'SELECT Q.*
FROM ComptTeam AS Q
WHERE Q.uidLifter IN
(
SELECT TOP ' + Cast(@TopParam as varchar(5)) + ' F.uidLifter
FROM ComptTeam F
WHERE F.TeamName = Q.TeamName
ORDER BY F.Points DESC, F.ScoreTotal DESC, F.uidLifter
)
ORDER BY Q.TeamName, Q.Points DESC , Q.ScoreTotal DESC'

EXEC sp_ExecuteSql @SomeString
(might have missed some quotes, but you get the general idea :o) )

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-31 : 07:41:51

lol, Lee-Z, it's an Access forum.....
I can suggest this imitation of a "parameterized" TOP K (select TOP K n from t order by n):

SELECT n
FROM t
WHERE
(select count(*) from t as b where b.n <= t.n) -
(select count(*) from t as c where c.n = t.n) + 1 <= [K];

... and ties will be included (just as Access does);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources