Solved

How to perform this SELECT?

Posted on 2009-07-10
4
166 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:brettr
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 300 total points
ID: 24827701
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' )
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24827735
select personID from yourtable where locked = true group by personid
0
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 100 total points
ID: 24827809
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

0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 100 total points
ID: 24827977
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

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 32
performance query 4 24
Alternative of IN Clause in SQL Server 3 21
Loops and updating in SQL Query 9 31
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question