January 13, 2009 at 4:22 pm
I think this is a real Newbie question but I'm uncertain where to start. I have listed below a CREATE TABLE and INSERT INTO statement for a sample of my table and its data, I hope this meets with the posting criteria.
--------------
--===== Create test db called bga
USE TempDB
CREATE TABLE bga
(
PrimKey int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
PatID int NOT NULL,
Sodium decimal(6, 1) NOT NULL,
Date_Time datetime NOT NULL
)
--===== Insert some test data
SET IDENTITY_INSERT bga ON
INSERT INTO bga
(PrimKey, PatID, Sodium, Date_Time)
Select '1','1234','137','Jan 13 2009 12:00:00' UNION ALL
Select '2','1234','130','Jan 13 2009 12:10:00' UNION ALL
Select '3','1234','131','Jan 13 2009 12:20:00' UNION ALL
Select '4','1234','128','Jan 13 2009 12:30:00' UNION ALL
Select '5','2345','134','Jan 13 2009 13:00:00' UNION ALL
Select '6','2345','137','Jan 13 2009 13:20:00' UNION ALL
Select '7','2345','132','Jan 13 2009 13:40:00' UNION ALL
Select '8','2345','135','Jan 13 2009 13:55:00' UNION ALL
Select '9','3456','131','Jan 13 2009 14:00:00' UNION ALL
Select '10','3456','136','Jan 13 2009 14:15:00' UNION ALL
Select '11','3456','134','Jan 13 2009 14:30:00' UNION ALL
Select '12','3456','132','Jan 13 2009 14:45:00'
--===== Basic Select Query
Select PatID,Date_Time,Sodium From bga
Order By 1,2
--===== What I want is the above data but only
--===== when the FIRST recorded Sodium, in Date_Time
--===== order for a PatID is Less Than 135.0
---------------------
The basic SELECT query is pulling out the data fields I want but in actual fact I only want this data when the first Sodium sample, in chronological order, for each PatID, is less than 135. In my test data my basic query pulls data for all three PatIDs when if fact what I want is only the four rows of data for each of the PatIDs 2345 and 3456.
I've tried numerous variations on the query but am just getting myself into knots.
Thanks for any help,
Ian.
January 13, 2009 at 8:45 pm
This'll do it:
Select PatID, Date_Time, Sodium
From bga
Where PatID IN(Select b2.PatID
From bga b2
Where 135.0 > b2.Sodium
And b2.Date_Time = (Select Min(b3.Date_Time)
From bga b3
Where b3.PatID = b2.PatID))
Order By PatID, Date_Time
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 11:16 pm
Thank you so much, that certainly does do it.
Cheers, Ian.
January 14, 2009 at 6:45 am
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply