On Wed, 2005-12-28 at 09:23 +1100, Nigel Weeks wrote:
>
> Let's break this query apart a little. I'm guessing that the 'LOGGED' field
> is a timestamp.

Yes, correct. However I have also tried in another scenario where it was
an integer, not a timestamp. Same results. It's not going to remain this
way, but the field LOGGED is also a Primary Key. So was integer variant
tested.

Still trying to decided if a timestamp is going to work for a primary
key for this table in this app. Low chance if ever of simulaneous to the
second transactions for this app, but it's not finalized either way.

> Looking at the inner part of the query, this will return three of the
> 'LOGGED' field, in descending order. Lets pretend that they are:
> '2005-12-24 9:50:00', '2005-12-24 9:52:00', '2005-12-25 00:05:00', and the
> person logging in was joe(which has no effect when it comes to the next bit)

Yes, 3 results in descending order. That part is fine and works. I run
query stand alone, I get 3 rows, and the intended results and output.

> Now the outer query.
> In plain english, this is:
>
> Get me the total number of statuses from the contact_login_log table where
> status = failed, AND any logins have occured on any of the following
> timestamps: '2005-12-24 9:50:00', '2005-12-24 9:52:00', '2005-12-25
> 00:05:00'
>
> So, if fifty people also tried to log in on those times as well, they'll be
> counted by your outer section as well. The login_name = 'joe' has no effect
> on the outer section.

Yeah, I need to also include anoter identifier there like
login_name='joe' had played with that as well, same results.
Here is a corrected version.

SELECT COUNT(STATUS) FROM CONTACT_LOGIN_LOG
WHERE LOGIN_NAME='joe' AND STATUS='Failed' AND LOGGED IN (
SELECT FIRST 3 LOGGED FROM CONTACT_LOGIN_LOG
WHERE LOGIN_NAME='joe' ORDER BY LOGGED DESC);

Being that LOGGED is a primary key, I thought having the login_name was
redundant or unneccessary?

> Can you re-describe what you actually want it to give you, and we'll see if
> we can recommend a query.
> If you want the last three failed logins:

I want to know the number of failed logins based on the last 3 attempts.
So if 2 failed login attempts, I want 2. 3, 3, 1,1.

I could easily do this client side and etc, but really want to do it
within the db. The query will be used in a trigger and/or stored proc.
Thought I might need to do it in a stored proc to return the value.
Seems one should be able to with a single query?

--
Sincerely,
William L. Thomson Jr.
Obsidian-Studios, Inc.
http://www.obsidian-studios.com