Solved

How to perform this SELECT?

Posted on 2009-07-10
4
161 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need help writing 2 Insert Query Scripts in SQL Server 2012 4 28
Update in Sql 7 8
Query 14 19
Merge Statement 3 0
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now