November 2, 2009 at 2:55 pm
I'm drawing a blank on what seems to be a simple SQL problem.
You have a name, a date and a score on a test. You took the test serveral times and I want to return ONLY 1 ROW which contains your score the FIRST time you took the test.
My table: test_scores
NameScoreDate
Mike901/1/09
Mike8011/28/09
select min(date),score,Name from test_scores group by Score,Name --RETURNS 2 ROWS
Of course my real problem has many students.
November 2, 2009 at 3:02 pm
In SQL Server 2005/2008 you can do this:
with FirstTest (
[Name],
[Score],
[Date],
[RowNum]
) as (
select
[Name],
[Score],
[Date],
row_number() over (partition by [Name] order by [Date] asc)
from
dbo.test_scores
)
select
[Name],
[Score],
[Date]
from
FirstTest
where
RowNum = 1;
November 3, 2009 at 10:33 am
(I am not on SQL 2005)
Here is the real query.....I got it to work but I guess the requirement is that I need the
First Blood Pressure/Response value taken for EACH patient.
SELECT VisitID,Response AS SYS_BP_A
FROM EdmPatientIntRepeatQueries o
WHERE VisitID IN(select VisitID from zbartemp) AND QueryID = 'VS.BP' AND
EXISTS (SELECT min(i.ActualDateTime),Response
FROM EdmPatientIntRepeatQueries i
WHERE i.VisitID IN(select VisitID from zbartemp) AND i.QueryID = 'VS.BP'
GROUP BY Response
HAVING min(ActualDateTime) = o.ActualDateTime)
Current Results:
VisitID Response
V123 125
V123 137
V456 118
V456 132
November 3, 2009 at 10:37 am
sextonk (11/3/2009)
(I am not on SQL 2005)Here is the real query.....I got it to work but I guess the requirement is that I need the
First Blood Pressure/Response value taken for EACH patient.
SELECT VisitID,Response AS SYS_BP_A
FROM EdmPatientIntRepeatQueries o
WHERE VisitID IN(select VisitID from zbartemp) AND QueryID = 'VS.BP' AND
EXISTS (SELECT min(i.ActualDateTime),Response
FROM EdmPatientIntRepeatQueries i
WHERE i.VisitID IN(select VisitID from zbartemp) AND i.QueryID = 'VS.BP'
GROUP BY Response
HAVING min(ActualDateTime) = o.ActualDateTime)
Current Results:
VisitID Response
V123 125
V123 137
V456 118
V456 132
Well, unfortunately you posted your request for help in a SQL Server 2005 forum, nor did you indicate in your original post that you are running SQL Server 2000. As a result you got a SQL Server 2005 solution.
Edit: I have asked to have this thread moved to a SQL Server 2000 forum.
November 4, 2009 at 9:39 am
It's not working because you're including the score which can differ. Think about your groupings as concatenations so 80Mike is different to 90Mike. What you need is exactly one row and you only want to group by name. You need to get the name and the minimum date and then append on the score associated with that date
Try this:
select subquery.date, test_scores.score, test_scores.Name
from test_scores join
(select Name, min(date) date from test_scores
group by Name) subquery
on test_scores.Name = subquery.Name
where test_scores.date = subquery.date
November 4, 2009 at 9:49 am
jritson (11/4/2009)
It's not working because you're including the score which can differ. Think about your groupings as concatenations so 80Mike is different to 90Mike. What you need is exactly one row and you only want to group by name. You need to get the name and the minimum date and then append on the score associated with that dateTry this:
select subquery.date, test_scores.score, test_scores.Name
from test_scores join
(select Name, min(date) date from test_scores
group by Name) subquery
on test_scores.Name = subquery.Name
where test_scores.date = subquery.date
I have to agree with jritson. I didn't read his entire post before taking my original 2005 code and rewriting the query for 2000.
Here it is, and if you notice, it is the same as above, just formatted differently.
select
ts.[Name],
ts.[Score],
ts.[Date]
from
dbo.test_scores ts
inner join (
select
ts1.[Name],
min(ts1.[Date]) as [Date]
from
dbo.test_scores ts1) dt
on (ts.[Name] = dt.[Name]
and ts.[Date] = dt.[Date])
November 4, 2009 at 11:19 pm
Alternatively you could use the following SQL (works in both 2K and 2005):
select
ts.[Name],
ts.[Score],
ts.[Date]
from dbo.test_scores ts
where ts.Date = ( select min(Date)
from dbo.test_scores
where [Name] = ts.[Name])
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply