Column with Iterated Row Count? - 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 2000 Forums

SQL Server Development (2000)

Column with Iterated Row Count?

Author

Topic

Lord Dubu
Starting Member

12 Posts

Posted - 2004-06-04 : 08:29:01

This might be a silly question...

I've got to harvest the date of the sixth transaction for a list of users who might have considerably more than 6 transactions.

There might be a better way to do this, but at present I was thinking I could create a view that would display a result like this:

bob, 7, 1, 2003-04-11
bob, 7, 2, 2003-04-12
bob, 7, 3, 2003-04-13
bob, 7, 4, 2003-04-14
bob, 7, 5, 2003-04-15
bob, 7, 6, 2003-04-16
bob, 7, 7, 2003-04-17

Where I'm stuck is the third column which displays an iterated count of rows returned by the query... any suggestions?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-04 : 08:49:42

you need a primary key in your table. Assuming there can be only 1 row per user per date, this will work for you:

select a.*
from
(
select a.*, (select count(*) from tbl b where a.user=b.user and a.date <= b.date) as Rank
from tbl a
) a
where Rank = 6

- Jeff

Lord Dubu
Starting Member

12 Posts

Posted - 2004-06-04 : 09:14:42

I'm sorry, I think I confused the issue in my description.

What I'd really like to know is how to insert a column into my table results that simply displays a sequential number for a row beginning at 1.

Being able to generate a table view that looks like the one I described is much more important to me than the actual specific query, as my query is being built by a search ASP page. The nature of the search is such that I need this sequential number, but I want to avoid having to insert it into the actual tables as a new column.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-04 : 10:22:08

try the following:

Select
Username,
UserId,
Counter = (Select count(*) From myTable as B Where UserId = A.UserId and tranDate<A.tranDate),
tranDate
From myTable as A
Order by Username, tranDate

Corey Aldebol

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-04 : 10:22:43

Nope...

The order of data in a database has no meaning...

If you want order, you need to order it...and then have the asp assign an artificial number...

If you need to assign an "order" it needs to be stored as data in a column.

Brett

8-)

Lord Dubu
Starting Member

12 Posts

Posted - 2004-06-07 : 15:55:52

I can produce similar results to Jsmith's suggestion using:

select m.cus, m., max(p.[date]), count(*) from dbo.purchase p, dbo.members m
where m.cus = p.cus and p.Date <= '2004-04-27'
group by m.cus, m.username
having count(*) = '6'

The only problem with both examples is they return everyone who has six transactions prior to the date supplied. I need to find users who have 6 or more transactions with the first 5 being before the date supplied...

Any suggestions?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-08 : 02:24:47

There are a couple of ways you can do this. One is to use the CASE expression and my favourite - a derived table!


SELECT * FROM
(
 SELECT m.cus, m.username, 
   COUNT(*) AS TotalPurchases, SUM(CASE WHEN p.Date <= '20040427' THEN 1 ELSE 0) AS EarlyPurchases
 FROM dbo.purchase p
 INNER JOIN dbo.members m
   ON m.cus = p.cus 
 GROUP BY m.cus, m.username
) A
WHERE EarlyPurchases >= 5 AND TotalPurchases >= 6

You could take Jeff's example and push it through a derived table to get the same result.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources