Top 3... - 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 2005 Forums

Transact-SQL (2005)

Top 3...

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-26 : 15:25:54

I want to return the top 3 results (transactions) for each customer, something like:

CustomerID TransactionID
---------- ------------
12345 1,2,3
7890 100,134,568

But the multi-valued column has me thinking that this could be pretty messy code, or not?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-26 : 15:29:53

In a relational database there is really no need for something like column values in CSV-style.
If this is for displaying purposes then do it in your front end.

No, you're never too old to Yak'n'Roll if you're too young to die.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-26 : 15:29:58

Do you really need it comma separated, or could you instead return the top 3 in 3 different rows per customer and then do the CSV from your application?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-26 : 15:39:10

Thanks; I don't need it in CSV up front.

As far as position, I've had no luck with row_number(). The transactions have corresponding transaction IDs but how to get at the second and third transactions boggles me at the moment. Obviously I can't subtract from max().

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-26 : 15:40:02

Could you post some sample data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-26 : 15:43:56

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

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

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-26 : 15:45:06

Let's say there are CustomerID and TranID, like so

CustomerID  TranID
----------- -----------
709615      549880
709688      161127
709690      159982

This is the max(TranID) of course, derived from a simple select-join expression. Two more columns for second and third would be great. The transactions are irregular in time. Could be two a day, then not another one for a week, etc.

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-26 : 15:56:11

Thanks, Peso. I was wondering about xml path and the like.

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-26 : 17:53:12

Back to using row_number() since I think it can give me what I need without too much complication, I'm almost there, but again I don't know how to limit the presentation to the first three rows for each customer (thus customerID listed 3 times).


WITH C AS (
SELECT DISTINCT
ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY b.TranID DESC) AS rownum,
c.CustomerID, b.TranID, b.EventDate
FROM Tran b
join CustomerLink cs
	on b.CustomerLinkID = cs.CustomerLinkID
join Customer c
	on cs.CustomerID = c.CustomerID
)
SELECT * FROM C

Customer1 has 31 rows, Customer2 has 2 rows, etc.

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-26 : 18:03:00

Got it! Select into a temp table, then filter by rownum for presentation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources