473,322 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Problem with nestd query

121 100+
Hi All,
scenerio is:
whenever user tries to change his password, his new password is sent to database and now i have a query which retrieves all the last changed password by this user with query (in my case only 5 matters)

Expand|Select|Wrap|Line Numbers
  1. select top 5 columnname from tablename where id=@id.
now what i am unable to do is to check his supplied password to be one of this 5 entries. means if he gives new password as the one which is already in his last 5 entries, he should be prompted to give some another password which should not be in last 5 entries

hope i am clear.

what i have tried id:

Expand|Select|Wrap|Line Numbers
  1. select count(*) from PwdHistory where @Suppliedpwd  
  2.  in (select top 5 columnname from PwdHistory where (UserId=@userid))
where @Suppliedpwd is new password supplied, @userid= userid of person we are checking last 5 passwords, columnname is column which contains passwords
any help will be appreciated
Apr 14 '09 #1
4 1516
mwasif
802 Expert 512MB
Moved to SQL Server forum.
Apr 14 '09 #2
ck9663
2,878 Expert 2GB
Let me see if I got you. You want to get the last 5 password changes of the user. If the new password that he wants today happens to be earlier than the top 5, you will allow it. If am reading you wrong, don't proceed coz this entire suggestion is wrong. Otherwise, read on...

Try this query.

Expand|Select|Wrap|Line Numbers
  1. if exists (select 1 from (select top 5 * from PwdHistory where UserId=@userid)  where @Suppliedpwd = columnname)
  2.  
This query will not really run as it is, you have to use function or SP.


--- CK
Apr 15 '09 #3
jay123
121 100+
thankks Ck for ur reply, yes u got me right and i just mangled ur provided query and the following query solves my purpose

Expand|Select|Wrap|Line Numbers
  1. select * from tablename where pwdcompare(@NewPassword,passwordcolumn,0)=1 and passwordcolumn 
  2.      in (select top 10 passwordcolumn from tablename where UserId=@UserId order by ID desc) 
Apr 15 '09 #4
create table #test (id INT identity(1,1), Password Varchar(10))
insert into #test (Password) values ('a')
insert into #test (Password) values ('b')
insert into #test (Password) values ('c')
insert into #test (Password) values ('d')
insert into #test (Password) values ('e')
insert into #test (Password) values ('f')
insert into #test (Password) values ('g')
insert into #test (Password) values ('h')
insert into #test (Password) values ('i')

Declare @NewPassword varchar(10)
set @NewPassword = 'a'

-- If the Result is 1 then the Password exists, user need to create new password.
-- If the Result is 0 then the Password not exists, so allow user need to save the new password.

Select count(1) as Result From
(select top 5 password from #test order by id Desc) A
where a.password = @NewPassword
-- Output
-- Result
-- 0

Declare @NewPassword varchar(10)
set @NewPassword = 'e'

Select count(1) as Result From
(select top 5 password from #test order by id Desc) A
where a.password = @NewPassword
-- Output
-- Result
-- 1

Use the filter for User and try it.

Reg,
JK
Apr 16 '09 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going...
8
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
1
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
4
by: Apple | last post by:
1. I want to create an autonumber, my requirement is : 2005/0001 (Year/autonumber), which year & autonumber no. both can auto run. 2. I had create a query by making relation to a table & query,...
6
by: lenny | last post by:
Hi, I've been trying to use a Sub or Function in VBA to connect to a database, make a query and return the recordset that results from the query. The connection to the database and the query...
3
by: Andy_Khosravi | last post by:
I have been trying to build a user friendly search engine for a small database I have created. I'm having some particular problems with one of my date fields. Here's the setup: I'm using...
20
by: Development - multi.art.studio | last post by:
Hello everyone, i just upgraded my old postgres-database from version 7.1 to 7.4.2. i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with copy-commands and to one file using...
3
by: Juan Antonio Villa | last post by:
Hello, I'm having a problem replicating a simple database using the binary log replication, here is the problem: When the master sends an update to the slave, an example update reads as follows:...
9
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.