How to join to a TOP 1 value? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to join to a TOP 1 value?

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-06-16 : 12:10:47

I'm in a bit over my head here so am looking for someone to help combine these 2 select statements into 1.
My database holds quiz results for certain skills and I need a SELECT statement that pulls out all the skills revelant to a certain user, then alongside each skill, pulls out the most recent quiz score they achieved in that skill's test. Hope that makes sense!

Here is my select statement that pulls out the list of skills for a person:


SELECT
    tbl_Skills.skillID,
    tbl_Skills.skillTitle,
    [tbl_SkillGroups-Skills].skillGroupID
FROM  tbl_Skills INNER JOIN
    [tbl_SkillGroups-Skills] ON tbl_Skills.skillID = [tbl_SkillGroups-Skills].skillID INNER JOIN
    [tbl_SkillGroups-Users] ON [tbl_SkillGroups-Skills].skillGroupID = [tbl_SkillGroups-Users].skillGroupID
WHERE
    ([tbl_SkillGroups-Users].userID = 3) AND 
    ([tbl_SkillGroups-Users].customerID = 2) AND 
    ([tbl_SkillGroups-Users].skillGroupArchived = 0)
ORDER BY 
    tbl_Skills.skillTitle

I have an additional table that holds the records for the quiz scores. It is called tbl_CompentencyHistory and has the following columns:

competencyID, int, PK

userID, int

skillID, int

skillLevel, numeric(5,2)

date, datetime

Can anyone decipher the select statement above to include only the MOST RECENT quiz score (skillLevel) from the above table for the skill being queried in each row of the select statement?

I've been trying all day and the closest I've got is below. The problem is that this statement pulls out EVERY test score instead of the most recent one only...


SELECT
    tbl_Skills.skillID,
    tbl_Skills.skillTitle,
    [tbl_SkillGroups-Skills].skillGroupID, 
    tmp.skillID AS Expr2,
    tmp.[date],
    tmp.skillLevel
FROM tbl_Skills INNER JOIN
    [tbl_SkillGroups-Skills] ON tbl_Skills.skillID = [tbl_SkillGroups-Skills].skillID INNER JOIN
    [tbl_SkillGroups-Users] ON [tbl_SkillGroups-Skills].skillGroupID = [tbl_SkillGroups-Users].skillGroupID LEFT OUTER JOIN
    (SELECT skillLevel, [date], skillID FROM tbl_competencyhistory WHERE userID = 3) tmp ON tmp.skillID = tbl_Skills.skillID
WHERE
    ([tbl_SkillGroups-Users].userID = 3) AND
    ([tbl_SkillGroups-Users].customerID = 2) AND 
    ([tbl_SkillGroups-Users].skillGroupArchived = 0)
ORDER BY tbl_Skills.skillTitle

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-16 : 12:53:10

This is a guess from your code - doesn't quite match your description but I suspect is more like what you want.
and tbl_CompentencyHistory.date = (select max(date) from tbl_CompentencyHistory t2 where t2.userID= tbl_CompentencyHistory.userID and t2.skillID = tbl_CompentencyHistory.skillID)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-06-16 : 13:10:30

Thanks for the post. Sorry to sound stupid but could you please show me whereabouts in my code to add your suggestion, as I'm not sure whether mine needs any modification first.
Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-16 : 13:43:01

add it to the end of the where clause.
I have put the latest entry per userid and skillid - don't know if that's what you want.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-06-19 : 02:50:42

This is giving an error, which I think is because I've not yet used the table in the query because I didn't know how to do it:
"The column prefix 'tbl_CompetencyHistory' does not match with a table name or alias name used in the query'.

There was a typo in my original post which I've correct in the statement - the correct table name is in fact tbl_CompetencyHistory.

When I stepped away from the problem over the weekend, I realised that the bit I'm stuck on can be singled out quite easily. In the quiz results table, I have a columns for the person, the skill (subject), the quiz score and the date. If I can just get the SQL search to work on this table alone then I can inner join the rest of the statement. Given the table below, is it possible to show only the most recent test score for the user for each subject??


ID   userID    skillID    score    [date]
----------------------------------------------
1    1         2          75       01/01/2005
2    1         2          69       05/01/2005
3    1         3          45       06/06/2006
4    1         3          67       06/06/2006
5    1         3          89       08/06/2006
6    1         4          100      12/12/2005

Therefore performing the search on the above data would return the results below (the most recent score for each skillID):


ID   userID    skillID    score    [date]
----------------------------------------------
2    1         2          69       05/01/2005
5    1         3          89       08/06/2006
6    1         4          100      12/12/2005

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-19 : 05:25:44

Hi R - here's one way...

--data
declare @t table (ID int, userID int, skillID int, score int, [date] datetime)
set dateformat mdy
insert @t
          select 1, 1, 2, 75, '01/01/2005'
union all select 2, 1, 2, 69, '05/01/2005'
union all select 3, 1, 3, 45, '06/06/2006'
union all select 4, 1, 3, 67, '06/06/2006'
union all select 5, 1, 3, 89, '08/06/2006'
union all select 6, 1, 4, 100, '12/12/2005'

--calculation
select * from @t a where date = (select max(date) from @t where skillID = a.skillID) order by ID

/*results
ID          userID      skillID     score       date                                                   
----------- ----------- ----------- ----------- ------------------------------------------------------ 
2           1           2           69          2005-05-01 00:00:00.000
5           1           3           89          2006-08-06 00:00:00.000
6           1           4           100         2005-12-12 00:00:00.000
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-06-19 : 06:43:27

Hi Ryan

Thanks for your input. I'm getting in a bit over my head, so please forgive me for asking for a little more information.

Seeing how you've written the select statement in your post, I believe similar logic could be applied directly to my entire quiz results table. The entire actual table looks like this:


ID   userID    skillID    score    [date]
----------------------------------------------
1    1         2          75       01/01/2005
2    1         2          69       05/01/2005
3    1         3          45       06/06/2006
4    1         3          67       06/06/2006
5    1         3          89       08/06/2006
6    1         4          100      12/12/2005
7    2         2          45       06/01/2005
8    7         6          98       03/05/2006
9    2         3          67       04/05/2006

Previously I had a select statement to just return all quiz results for 1 particular user (the results I posted in my example table above), but I think now it might be just as easy (for experts such as yourselves!) to perform the whole search in one operation rather than as a joined affair which I originally planned.

Is it possible to perform a search passing in the userID as a parameter, to get out the latest quiz score (score) for each skill (skillID) test taken?

I've had a go based on your suggestion but it's pulling out only 1 result which I can't figure out why!


SELECT
        *
FROM
        tbl_CompetencyHistory a
WHERE
       ([date] = (SELECT MAX(date) FROM tbl_competencyHistory WHERE skillID = a.skillID)) AND 
       (userID = 1)
ORDER BY
       skillID

I really hope that makes sense!? A solution to this would really make my day!!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-19 : 07:00:53

Try:

SELECT
        *
FROM
        tbl_CompetencyHistory a
WHERE
       ([date] = (SELECT MAX(date) FROM tbl_competencyHistory WHERE skillID = a.skillID and userid = a.userid)) AND 
       (userID = 1)
ORDER BY
       skillID

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-06-19 : 09:23:11

Hey Ryan
Tried the suggestion but it finds no 0 results. Any ideas...?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-19 : 09:50:27

Oh? This seems to work for me. If you run the code below, do you get the same results?

--data
declare @tbl_CompetencyHistory table (ID int, userID int, skillID int, score int, [date] datetime)
set dateformat mdy
insert @tbl_CompetencyHistory
          select 1, 1, 2, 75, '01/01/2005'
union all select 2, 1, 2, 69, '05/01/2005'
union all select 3, 1, 3, 45, '06/06/2006'
union all select 4, 1, 3, 67, '06/06/2006'
union all select 5, 1, 3, 89, '08/06/2006'
union all select 6, 1, 4, 100, '12/12/2005'
union all select 7, 2, 2, 45, '06/01/2005'
union all select 8, 7, 6, 98, '03/05/2006'
union all select 9, 2, 3, 67, '04/05/2006'

--calculation
SELECT
        *
FROM
        @tbl_CompetencyHistory a
WHERE
       ([date] = (SELECT MAX(date) FROM @tbl_competencyHistory WHERE skillID = a.skillID and userid = a.userid)) AND 
       (userID = 1)
ORDER BY
       skillID

/*results
ID          userID      skillID     score       date                    
----------- ----------- ----------- ----------- ------------------------
2           1           2           69          2005-05-01 00:00:00.000
5           1           3           89          2006-08-06 00:00:00.000
6           1           4           100         2005-12-12 00:00:00.000
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-06-19 : 10:07:33

Hi Ryan
When I run the whole lot in Query Analyzer, I get the same results as you. What I'm struggling with is applying the search logic back into my existing test result table. I'm fairly new at anything beyond simple T-SQL, so please excuse my ignorance.

What is the first section of your code example actually doing? (declare @tbl_CompetencyHistory....)
I presumed it's creating a temporary table which is then searched from. I therefore also presumed that if I just take the '--calculation' chunk you posted and change the name of the table to my actual table, the search would work, but in reality it doesn't find any results.

What am I doing wrong??!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-19 : 10:20:35

R - You've assumed and done the right thing. The '--data' chunk is just setting up a temporary table with some test data.

Now, in your actual table, are you sure you have a record with a userid of 1? That's the only thing that would cause 0 rows to be returned (I think).

You could try removing the "AND (userID = 1)" bit (temporarily) to see if you then get some results. If you do, that might help you figure things out...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-06-19 : 13:35:27

I've got it working!

Had a tiny typo that was causing strange stuff to happen.

Many thanks for your help and patience in this post. I'll get something more challenging for you in the near future...! ;-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources