Looping in a stored procedure - 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 2000 Forums

Transact-SQL (2000)

Looping in a stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-21 : 08:02:16

Dave writes "Ok, I'm not sure if I'm missing something obvious here, but basically I have a "logins" table. This stores the MemberID and Date that each member has logged into a site.

I would like to query this table in a range from the beginning of the month to the end of the month, for each member that has logged into the site and the date they logged in. However, I would like to limit the query to a maximum of 5 records returned PER USER.

To be a little more clear, if Member 1 has logged in 10 times in a month (or the range specificied by the application form), then it returns the dates he's logged for the first 5 times, if Member 2 has logged in 3 times, the it returns the dates she logged in 3 times and so on for each member that has logged in between the specified dates.

Hope this makes sense and many, many thanks in advance.

OS: Windows Advanced Server 2000 SP4
SQL Server: SQL Server 2000 SP3 or 3a"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-11-21 : 08:28:43

There are various ways to write this query. Here is one:


create table dave (
    username varchar(10),
    loggedon datetime)

insert dave
select 'a',0
union select 'a',1
union select 'a',2
union select 'a',3
union select 'a',4
union select 'a',5
union select 'b',0
union select 'b',1
union select 'b',2
union select 'b',3
union select 'b',4
union select 'b',5


select
    username,
    loggedon
from
    dave d
where
    exists (
        select 1
        from
            dave
        where
            username = d.username and
            loggedon = d.loggedon
        group by
            username
        having (
            select
                count(*)
            from
                dave
            where
                username = d.username and
                loggedon < d.loggedon) < 5)
order by
    username,
    loggedon

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-21 : 08:39:29

Another way, this one only needs to reference the "dave" table twice instead of 3 times:


select * 
from (
  select
    username,
    loggedon,
    (select count(*) 
     from dave
     where d.username = dave.username and
          d.loggedOn >= dave.LoggedOn) as LogOnNumber
  from
    dave d
) A
where LogOnNumber <=5

- Jeff

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-11-21 : 08:53:41

Neatness however seems to sacrifice performance in this instance.....with the small dataset, the former seems to outperform the latter (as per execution plan) by 2:1 when no indices present on Dave...and by 4:1 when an index exists on (username,loggedon).

Off the top-of-your-heads, which should scale better?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-21 : 09:01:04

Actually ... i showed the OPPOSITE ...

I ran the same test, it looked like the shorter outperformed the longer by 2-1 .... and then with an index, the shorter still outperformed but by not quite as much -- like 1.3 to 1.

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-11-21 : 09:08:05

We've done the Top x of a Group discussion before. Fribble, as is typical, is the fastest ....

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources