February 20, 2008 at 7:57 am
Do they have to be grouped somehow or ordered in the result set?
February 20, 2008 at 8:34 am
NO , dont have to grouped or ordered
February 20, 2008 at 8:47 am
TOP xx entails an order (you have to establish a ranking of some kind in order to figure out which ones are "top"), so - you I'm thinking you actually meant to say "Yes - they're in order by T3"
In which case:
select Top 3
T1,T2,T3
from MyTable
order by T3
Of cuorse - I'm probably missing something...:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2008 at 8:47 am
If you can better explain what you're trying to do, maybe we can come up wiht something. from the tables and code, it isn't obvious what the criteria is on which you're joining (we don't know what the temp tables contain) and what's the ordering.
February 20, 2008 at 8:55 am
let me put it this way
T1 T2 T3
90 9 5
90 0 3
90 1 8
90 2 2
91 3 4
91 4 1
91 5 6
91 6 7
91 8 2
92
92...........
output should be
OUTPUT:
OUTPUT:
T1 T2 T3
90 2 2
90 0 3
90 9 5
91 4 1
91 8 2
91 3 4
92.......
it should get top 3 records based on T1 column
and T2 is primary key in this table if that helps
February 20, 2008 at 9:08 am
So, the TOP keyword in the SELECT is not working for you?
February 20, 2008 at 9:20 am
You're looking to filter on a grouped running count. Meaning - create a running count (which then "creates" the ranking for the TOP x concept), and then filter by runningCount<4.
There's a huge thread on this over here:
http://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx
In the meantime - try this on:
--set up the test
create table mytable (T1 int,T2 int ,T3 int)
Insert mytable
select 90, 9, 5 UNION ALL
SELECT 90, 0, 3 UNION ALL
SELECT 90, 1, 8 UNION ALL
SELECT 90, 2, 2 UNION ALL
SELECT 91, 3, 4 UNION ALL
SELECT 91, 4, 1 UNION ALL
SELECT 91, 5, 6 UNION ALL
SELECT 91, 6, 7 UNION ALL
SELECT 91, 8, 2
--create the temp table to be used
select *, 0 RankNum into #mytable from mytable
Create clustered index pxmytable on #mytable(T1,T3)
--get some variables together
Declare @prevT1 int
declare @running int
select @prevt1=0,@running=0
--now create the ranking
UPDATE #Mytable
set @running=RankNum=case when @prevt1=T1 then @running+1 else 1 end,
@prevT1=T1
from #MYtable with (index(pxmytable),tablockX)
--display what you want
select * from #mytable where RankNum<4
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2008 at 11:57 am
Susane, Matt is spot on... you won't find a faster way to do that especially in light of a million rows or so, in my humble opinion.
Undoubtedly, you will run into some folks who recommend something like the following (with or without an order by in the sub-query)...
SELECT mt1.*
FROM MyTable mt1
WHERE mt1.T2 IN (SELECT TOP 3 T2
FROM MyTable mt2
WHERE mt2.t1 = mt1.t1)
... and, yes, it will work and it looks very tempting to use because it looks small and tight. But, it does use a correlated subquery and that's RBAR (see my tagline below). I'm bringing all this up because the code Matt wrote looks a bit complicated compared to the above... yet, the way above can raise all sorts of performance problems. Be careful...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply