?
Solved

How to perform this SELECT?

Posted on 2009-07-10
4
Medium Priority
?
170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1200 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 143

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 400 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 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 400 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

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

741 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