Help with T-SQL

Question

Help with T-SQL

Aspurr

SSC Rookie

Points: 35
More actions
January 23, 2007 at 6:35 am

#175540

Hello,
I am new to SQL scripting, and need advice on creating a script that interrogates a column which can have the same data, but I need to select every 'nth' occurence, and return all data in other columns that correspond. To try and explain
The table contains 3 columns
Phrase, SearchedOn, SessionID
The session ID can have the same entry where that SessionID populates a different Phrase and time and so has multiple entries
I need to select every 100th SessionID and display that SessionID and all data in Phrase and SearchedOn that corresponds to it.
Any help appreciated as I have read that using cursors and temporary tables can be bad

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

That you are asking for every 100th SessionID suggests that your data is ordered in some way. If not, what does "every 100th" mean? And please confirm that you want every 100th value as opposed to every 100th distinct value.

You're right about cursors: there's nearly always a better set-based way of doing it. However, temporary tables certainly have their time and place and should be used if they provide the best way of getting your result.

John

Aspurr SSC Rookie Points: 35 More actions · Answer 2

Thanks for the quick reply John

The data is not ordered. Basically the report that has been requested is trying to find trends of phrases grouped by SessionID

The every 100th is a random number (there are 100's of thousand rows) that has just been chosen, so I need to take every 100th SessionID, but display all Phrases and SearchedOn data associated with that SessionID

for example. If I said I want every other distinct session id

Phrase SearchedOn SessionID

Some text 2006-12-04 03:03:01.000 c9cb6fb8

some text 2006-12-04 03:02:19.000 c9cb6fb8

Some more text 2006-12-04 08:54:17.653 d8459225

different text 2006-12-04 09:35:39.153 86a42462

I hope this helps 2006-12-04 09:44:12.440 370fa762

cat and dog 2006-12-04 11:53:18.480 b2c21aed

cat and dog 2006-12-04 11:53:58.430 b2c21aed

cats and dogs 2006-12-04 13:46:25.600 b2c21aed

more cats and dogs 2006-12-04 13:48:11.520 b2c21aed

It would select the SessionID's in bold being, but return all other data associated with that SessionID again in bold

I hope this gives more clarification

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 3

How often is this report going to be run. A different approach to a relevant report would be to select the top 2 search phrases from each session id, or better yet the top search phrases and how many sessions issued those searches. Or session and how many unique searches they did.

Just some ideas.

Tom

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 4

This should work, although I haven't tested it. I don't know how it'll perform on a large table. I can't think of a way without using dynamic SQL to select every 100th row, so I've done it so it chooses 2% of the rows at random. You can alter this figure if you need.

select phrase, searchedon, SessionID

from MyTable m join

(select distinct SessionID from

(select top 2 percent SessionID

from MyTable order by newid()) s) t

on m.SessionID = t.SessionID

John

Aspurr SSC Rookie Points: 35 More actions · Answer 5

Hello Tom,

Once I have an understanding on how to build the query it will become a stored procedure and run daily, populating another table with the results and then using SQL Reporting Services for the users to see the results

Cheers