effiecient way to get top 5 - 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 2008 Forums

Transact-SQL (2008)

effiecient way to get top 5

Author

Topic

sunnytrader
Starting Member

3 Posts

Posted - 2011-07-24 : 22:16:36

Hello,

My aim is to get minimum 5 records from a table. The condition will be strictest in first go (check on most columns) and then if that dosent get 5 results then a lenient check is done.

currently i am doing something like

if(select count(0) from table where condition) > 4
begin
select * from table where condition
end
else
begin
if(select count(0) from table where newcondition) > 4
begin
select * from table where newcondition
end
else
begin
...........
end
end

Is there a netter way to do this?
Thanx in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-24 : 23:27:18

Have you considered using the ROW_NUMBER() function?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 00:29:50

can you give more information on what all conditions you will be checking? will they be common set from where you will keep on dropping some conditions whenevr you wont get enough records back?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eralper
Yak Posting Veteran

66 Posts

Posted - 2011-07-25 : 04:03:56

Hello Sunny,

You can use a temp table and populate this table in each step until 5 records is selected.

In the most strict condition, if there is only one row you can continue with next step. Of course first you should use row count in temp table.

According to the previously selected rows, you might require to choose 3 rows from the second select for example.

You can adopt your script for a temp table use.

-------------
Eralper
http://www.kodyaz.com

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2011-07-26 : 07:51:30

The easiest might be to ORDER BY a calculated score for each row using CASE with a TOP 5 clause. You can create a computed column and an index on it if it's a large table.

select top 5 * from
blah
order by
case when condition1 then 1
case when condition2 then 2
end;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources