Top 3 records

Question

Post reply

Top 3 records

Susane

SSC-Addicted

Points: 491
More actions
February 20, 2008 at 7:49 am

#71171

T1T2T3
9095
9003
9018
9022
9034
9041
9056
9067
i have a tbale something like this and iam trying to get the output as below
OUTPUT:
T1T2T3
9041
9022
9003
for that iam using joins and unions
below is my code
SELECT P1.* FROM #TEMP P1 INNER JOIN (
SELECT T1,Min(T3) AS PRIORITY3 FROM #TEMP
GROUP BY T1) P3 ON P3.T1 =P1.T1 AND PRIORITY3=T3
AND P1.T1= 90
UNION
SELECT P2.* FROM #TEMP P2 INNER JOIN (
SELECT T1,MIN(T3) AS PRIORITY1 FROM #TEMP WHERE T2 NOT IN (
SELECT T2 FROM #TEMP P1 INNER JOIN (
SELECT T1,Min(T3) AS PRIORITY3 FROM #TEMP
GROUP BY T1) P3 ON P3.T1 =P1.T1 AND PRIORITY3=T3
)GROUP BY T1) P1
ON P2.T1= P1.T1 AND PRIORITY1= P2.T3
AND P2.T1= 90
union
.
.
.
is there any better way of doing it
any help is appreciated

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ghostrider SSC Enthusiast Points: 119 More actions · Answer 1

Do they have to be grouped somehow or ordered in the result set?

Susane SSC-Addicted Points: 491 More actions · Answer 2

Susane

SSC-Addicted

Points: 491

February 20, 2008 at 8:34 am

#780225

NO , dont have to grouped or ordered

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

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?

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

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.

Susane SSC-Addicted Points: 491 More actions · Answer 5

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:

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

ghostrider SSC Enthusiast Points: 119 More actions · Answer 6

So, the TOP keyword in the SELECT is not working for you?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

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?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)