By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

HELP with SQL Statement...urgent!

P: n/a
Hi,

I have a table that lists all attempts at logging in to a server.
Columns are:
userid, firstname, lastname, login_date, login_time

If a user attempted to login 5 times, then I want to return the row
(with all columns)......
BUT I only want to return the row with the 5th time attempted.

something like this:
select * from mytable
join
select userid, login_date from mytable
group by userid, login_date having count(*) > 4
BUT, how do I get just the 5th time attempted??

Thanks!
Skiz

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
This will select it for a particular user:

Select top 1 userid, firstname, lastname, login_date, login_time
from tblTable
Where userid = 12345
Order by login_time desc

zi******@loretel.net wrote:
Hi,

I have a table that lists all attempts at logging in to a server.
Columns are:
userid, firstname, lastname, login_date, login_time

If a user attempted to login 5 times, then I want to return the row
(with all columns)......
BUT I only want to return the row with the 5th time attempted.

something like this:
select * from mytable
join
select userid, login_date from mytable
group by userid, login_date having count(*) > 4
BUT, how do I get just the 5th time attempted??

Thanks!
Skiz

Jul 23 '05 #2

P: n/a

<zi******@loretel.net> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hi,

I have a table that lists all attempts at logging in to a server.
Columns are:
userid, firstname, lastname, login_date, login_time

If a user attempted to login 5 times, then I want to return the row
(with all columns)......
BUT I only want to return the row with the 5th time attempted.

something like this:
select * from mytable
join
select userid, login_date from mytable
group by userid, login_date having count(*) > 4
BUT, how do I get just the 5th time attempted??

Thanks!
Skiz


Unfortunately, you haven't really given enough information to answer the
question properly - you need to post CREATE TABLE and INSERT statements to
set up a test case, otherwise we can only guess about data types, keys and
what your data looks like:

http://www.aspfaq.com/etiquette.asp?id=5006

One potential issue with what you've posted is that you seem to have date
and time in separate columns - in MSSQL, these would normally be in a single
datetime column, which makes ordering by login_time or finding the most
recent login_time much easier. Also, what happens if a user attempts to log
in 6 times - do you still want to see the 5th attempt, not the 6th? And what
about older data - are rows deleted once a user has successfully logged in?
If not, then which attempts should count towards the total of 5 - ones in
the last hour, the last day, the last week, the last year?

Simon
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.