How to find top 3 rows in the same table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to find top 3 rows in the same table?

Author

Topic

atlzbest
Starting Member

7 Posts

Posted - 2010-06-03 : 15:02:15

Hi All,

I am having trouble putting this query together, any tips would be helpful.

The table structure looks like this

CustomerNum PostDate OfferAccepted OfferName
1000 5/1/10 Y Test1
1000 5/1/10 Y Test2
1000 5/1/10 N Test3
1001 5/1/10 N Test1
1001 5/1/10 N Test2
1001 5/1/10 Y Test3

I want to flatten the table by each CustomerNum e.g.

CustomerNum PostDate OffAcctd OfferName1 OfferName2 OfferName3
1000 5/1/10 Y Test1 Test2 Test3
1000 5/1/10 Y Test3

Reason for that is i have an older process that updates a dest. table that looks like the above flattened table. I am adding a new source and trying to do a UNION of the two tables so i need the newer one flattened. The only important thing above is I only keep the CustomerNumbers is they have atleast 1 OfferAccepted=Y and no more than 3.
Sorry the table cell alignment is a bit off.

Thanks in advance!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-03 : 15:32:43

DECLARE @table table (CustomerNum int, PostDate datetime, OfferAccepted char(1), OfferName varchar(10))

INSERT INTO @Table
select 1000,'5/1/10','Y','Test1' UNION ALL
select 1000,'5/1/10','Y','Test2' UNION ALL
select 1000,'5/1/10','Y','Test3' UNION ALL
select 1001,'5/1/10','N','Test1' UNION ALL
select 1001,'5/1/10','N','Test2' UNION ALL
select 1001,'5/1/10','Y','Test3'


SELECT customerNum,postDate,OfferAccepted
     ,MAX(CASE WHEN rank = 1 THEN OfferName END) as OfferName1
     ,MAX(CASE WHEN rank = 2 THEN OfferName END) as OfferName2
     ,MAX(CASE WHEN rank = 3 THEN OfferName END) as OfferName3
     

FROM
(
select customerNum,postDate,OfferAccepted,OfferName
       ,[Rank] = rank() over(partition by customerNum,postDate order by offername) 

 from @table
where OfferAccepted = 'Y'
) t

GROUP By
 customerNum,postDate,OfferAccepted

Jim

P.S. you can use the

tags to keep your columnsline up

Jim

Everyday I learn something that somebody else already knew

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2010-06-03 : 16:33:23

SELECT customerNum,postDate,OfferAccepted
,MAX(CASE WHEN OfferName = 'Test1' THEN 'Test1' END) as OfferName1
,MAX(CASE WHEN OfferName = 'Test2' THEN 'Test2' END) as OfferName2
,MAX(CASE WHEN OfferName = 'Test3' THEN 'Test3' END) as OfferName3

FROM
@Table
WHERE OfferAccepted = 'Y'
GROUP By customerNum,postDate,OfferAccepted
ORDER BY customerNum

atlzbest
Starting Member

7 Posts

Posted - 2010-06-07 : 10:11:26

Thanks guys, this is a fantastic start!

What would i need to do to find a relationship like: CustomerNum record 1 ALWAYS has a OfferAccepted= Y and the remaining 2 records it doesn't matter whether OfferAccepted is Y or N

i am thinking of a corelated query or a self-join but i am failing at writing one.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-07 : 12:00:51

quote:
Originally posted by atlzbest

Thanks guys, this is a fantastic start!

What would i need to do to find a relationship like: CustomerNum record 1 ALWAYS has a OfferAccepted= Y and the remaining 2 records it doesn't matter whether OfferAccepted is Y or N

i am thinking of a corelated query or a self-join but i am failing at writing one.

I'm sorry...but can you elaborate. Can you show us the expected output. I thought Jim's query already provided your expected output.

atlzbest
Starting Member

7 Posts

Posted - 2010-06-07 : 12:22:26

Sorry guys!

Here goes:

create table #table2
(
CustomerNum int,
PostDate datetime,
OfferAccepted char(1),
OfferName varchar(10),
)
insert into #table2
select 2001, '5/1/10', 'Y', 'Test1' union
select 2001, '5/1/10', 'N', 'Test2' UNION
select 2001, '5/1/10', 'N', 'Test3' UNION
select 2001, '5/1/10', 'N', 'Test4' UNION

select 2002, '5/2/10', 'Y', 'Test3' union

select 2003, '5/2/10', 'N', 'Test1' UNION
select 2003, '5/2/10', 'N', 'Test2' UNION
select 2003, '5/2/10', 'N', 'Test4' UNION
select 2003, '5/2/10', 'N', 'Test5'

Desired output is

CustomerNum PostDate Offer1 Offer2 Offer3
2001 5/1/10 Test1 Test2 Test3
2002 5/2/10 Test3

Explanation: Pick top 3 offers by CustomerNum. CustomerNum must have atleast 1 offer with OfferAccepted = Y. Display Top 3 offers as columns (only 1 record per CustomerNum)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-07 : 12:32:34

That's what my query produces. Am I still not getting what you want in the new query?

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources