sql concept help 1

[webguyz]

I'm trying to generate a little report based on some data.

The data structure is like this:

user_id      page_number
------------------------
1            22
1            21
1            20
3            18
3            17
6            23
6            22

...
...

Basically it's a list of user_ids from a user accounts table, and the page_number is the number of the page that that particular user navigated to in the search results on our site. So if I searched for some topic, and got 8 pages of results back, and clicked on the page 7 link, that would be recorded as (my user id) | 7.

What I need to do is pull the top 10 user ids out of this based on the highest page number they reached. This presents a problem because I need distinct user ids based on the max page_number, but can't form a SQL query to give me this! It's frustrating me so I seek the help of others.

Here's what I've got so far, tell me if I'm on the right track:

SELECT TOP 10 a.user_id
FROM activity_table a
GROUP BY a.user_id
HAVING a.page_number = (
   SELECT MAX(page_number) 
   FROM activity_table 
   WHERE user_id = a.user_id
)

The idea here is "get the top 10 user ids from this table where the user id equals this other user id selected based on it being the max page_number for this user id in the table".

Obviously this doesn't work as I have some contraint issues with the page_number variable not being in a function.

Any ideas? Do I need to use a cursor here and some actual manual comparison/logic to handle this, or can it be done with SQL lingo?? Thanks!

 

[webguyz]

Hah! Well looks like I just solved my own problem. I knew I needed to group differently and order the results, but I didn't think this would actually work! But it does.

SELECT TOP 10 a.user_id, a.page_number
FROM activity_table a
GROUP BY a.user_id, a.page_number
HAVING a.page_number = (
   SELECT MAX(page_number) 
   FROM activity_table 
   WHERE user_id = a.user_id
)
ORDER BY a.page_number DESC

 

[r937]

cursor? whoa, no way

what you need to do is first find the highest page each user got to --

[tt]select user_id
, max(page_number) as maxpage
from activity_table
group
by user_id[/tt]

now, this will guarantee to get you one row per user_id, right?

okay, so now just sort them into descending sequence by page_number and pull the top ten

i hesitate to suggest the use of the TOP keyword as it isn't ANSI SQL (this forum) -- unless perhaps it was added in SQL2003 or something (i'm sure someone will let me know quickly if that's the case, but i personally do not have a copy of the latest SQL standards to check this for myself)

however, since you've already mentioned TOP, you must be using a database that supports it, so...

[tt]select top 10
user_id
, max(page_number)
from activity_table
group
by user_id
order
by maxpage desc[/tt]

rudy

http://r937.com/

 

[webguyz]

Rudy hey man, how are you? I remember you from the evolt mailing list, which I am no longer a part of (too much email these days). Thanks for the tip, you were always the db guru.

So my sql above is just a longer version of what you wrote there, correct? Probably less efficient too.

 

[webguyz]

Rudy, just tried your suggestion, and what it is doing is returning the max (highest) page_number records, with associated user_ids. So for example:

1|20
2|21
2|20
2|19
3|15
5|2
5|1
6|12
7|13

If I wanted to get the top 3 user_ids based on the max page_number they reached, my statement above would give you:

2|21
1|20
3|15

Whereas your statement yields:

2|21
2|20
1|20

See what I'm getting at?

And I forgot to answer, yes I'm working w/ SQL Server here, so proprietary non-standard lingo is essential.

 

[r937]

hi webguyz, sorry, the name "webguyz" does not ring a bell as far as evolt is concerned

i finally quit evolt a couple months ago (for reasons too numerous to mention), after having been there for five years (i was one of the original founders)

now i just hang out on the various database forums like tek-tips and others

the query i gave you should actually be the same as the one you wrote (the one with the correlated subquery) but you said "Obviously this doesn't work as I have some contraint issues with the page_number variable not being in a function" which i, like, totally did not understand, so i just took a different approach to it

 

[r937]

there's no way my query could have possibly returned both 2|
21 and 2|20

there's a GROUP BY on the userid and that gaurantees only one row per user_id, specifially 2|21

what am i not seeing?

 

[webguyz]

lol, I was webguy on that list, only on it for a few months though. You are a legend as far as I'm concerned! I mean that in a not-old-just-know-it-for-sure way, promise.

So you are right again, I, um, lazily edited my original query to slide yours in with minimal wrist strain and forgot the max function, so now all is well and 3x as fast!

Thanks again and take care.

 

[r937]

thanks for the kind words

Rochester Institute of Technology?

yes, i remember

 

[webguyz]

Yeah that was me. I really should finish up that degree at some point... <sigh>

Man that was some time ago, back when I was creating this CMS for the company I work for. Remember helping me with that? You introduced me to the having clause, which I've been abusing ever since then with much satisfaction. =) Articles, paragraphs... ring a bell?

 

[r937]

not the specific thread, no, i just googled for your name and recognized it (oops, i guess i've given away your secret now, eh)

 

[webguyz]

heh, google can be too revealing sometimes I tell you. There is stuff from like 1996 that I participated in that somehow got indexed in the google groups &quot;bank of knowledge&quot;. We're talking serious old school at that point, at least for me.

Anyhow I digress, thanks again for the help, sometimes my brain just doesn't think sql like it should.