• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

How to perform this SELECT?

I have a table with these columns and values:

lockID     personID      locked                date
1                 8                   True             7/10/2009 8:43:26 PM
6                13                   True             7/10/2009 9:12:24 PM
7                13                   False             7/10/2009 9:12:24 PM      
8                14                   True             7/10/2009 9:12:39 PM
9                14                   False             7/10/2009 9:12:57 PM      
10                16                   True             7/10/2009 9:16:44 PM
11                16                   False             7/10/2009 9:17:08 PM
12                17                   True             7/10/2009 9:27:15 PM
13                18                   True             7/10/2009 9:27:17 PM

I'd like to check if any one has an open lock.  The results from that query should be the following list of personIDs: 8, 17, 18.  What should that type of query look like?  Does the current table support it?
3 Solutions
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT  a.PersonID
FROM urTable a
WHERE Locked = 'True' and NOT EXISTS (SELECT 1 FROM urTable b  where a.personID = b.personID and b.Locked = 'false' )
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select personID from yourtable where locked = true group by personid
Since you mention that from the above dataset the result should be 8, 17, and 18 I presume you are looking for lock to be truu for a person on its last occurence, since there could be mulitple enteries. I would do something like this:
Select m.PersonID
from (
Select p.PersonID, p.Locked
From   YourTable p INNER JOIN (Select PersonID, MAX(Date) Dt From YourTable Group by PersionID) t
       ON p.PersonID = t.PersonID and
          p.Date = t.Dt
     ) m
Where m.Locked = 'True'
Group by m.personID

Open in new window

Kevin CrossChief Technology OfficerCommented:
To add on to Aneesh's answer, you can add in a check on date like this:
SELECT  a.PersonID 
FROM urTable a 
WHERE Locked = 'True' 
   and NOT EXISTS (SELECT 1 FROM urTable b  where a.personID = b.personID and b.Locked = 'false' and b.date >= a.date)

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now