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.
| Author |
Topic |
|
thedude813
Starting Member
3 Posts |
 Posted - 2008-08-28 : 10:21:28
|
| Hey all, I'm having some issues with an aggregate function question. Basically I have the following in two separate tables - student information and tests they have taken. I need to get information from both of them based on the latest test they have taken. What I have is the following:Select s.std_pk, t.test_pk, max(t.test_dt)from student s, test t, stdtstFK stwhere s.std_pk = st.std_fk and t.test_pk = st.test_fkgroup by s.std_pk, t.test_pkI know this isn't right. What I want is to be able to get the student pk, and the test pk of the latest date. Whenever I write something up, it either gives me all entries of tests taken (as in here, because the test_pk's are different, so grouping by them will give me distinct values for each std_pk/test_pk pair). The other option is that I can get all the student_pk's with their max test_dt's, but I don't like this answer because I am concerned that when I link that date back I could get multiple rows if on some rare case their two latest tests had the same date. This shouldn't happen, but I want to be able to rely on the query, not on the data. I would much rather be able to immediately grab the test_pk that is linked to the max(test_dt) as I select it, as opposed to trying to link a date back to a pk, it just seems unwieldy. Is there any other way to do this? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-28 : 10:25:38
|
may be thisSELECT *FROM(SELECT ROW_NUMBER() OVER (PARTITION BY s.std_pk, t.test_pk ORDER BY t.test_dt DESC) AS Seq,*FROM student sINNER JOIN stdtstFK stON s.std_pk = st.std_fk INNER JOIN test tON t.test_pk = st.test_fk)rWHERE r.Seq=1 |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-28 : 10:28:46
|
| Also replace * with actual namesMadhivananFailing to plan is Planning to fail |
|
|
|
thedude813
Starting Member
3 Posts |
Posted - 2008-08-28 : 10:45:32
|
| Hey, thanks for the quick and helpful responses! I thought I should just post again to let everyone know what was up. I tried your code, but it was giving me only 1's for the whole seq row, so I decided to look into how you were doing it.The OVER( PARTITION BY... statement is something I never even knew existed, but is apparently very important for the issue I was having. Your code was very close to what I needed, and all I had to do was remove t.test_pk from the partition by clause. It should be making separate partitions for each employee, not for each employee/test set.Thanks again for all your (very fast) help! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-28 : 10:50:46
|
quote:
Originally posted by thedude813
Hey, thanks for the quick and helpful responses! I thought I should just post again to let everyone know what was up. I tried your code, but it was giving me only 1's for the whole seq row, so I decided to look into how you were doing it.The OVER( PARTITION BY... statement is something I never even knew existed, but is apparently very important for the issue I was having. Your code was very close to what I needed, and all I had to do was remove t.test_pk from the partition by clause. It should be making separate partitions for each employee, not for each employee/test set.Thanks again for all your (very fast) help!
ok...i just made the query from what you posted so couldnt guess you were looking for distinct result per employee. It would be best if you can give some sample data & output in future so that we can know what you're trying to get else you will keep us guessing abt what you really want. Glad that you could sort it out. |
|
|
|
thedude813
Starting Member
3 Posts |
Posted - 2008-08-28 : 10:56:10
|
| Yeah, I knew I wasn't being clear when I wrote it, but I tried the best I could. I said that "What I want is to be able to get the student pk, and the test pk of the latest date." This was a little ambiguous. Anyways, it doesn't matter, you got me close enough to the answer to get it all figured out. Thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-28 : 10:59:02
|
quote:
Originally posted by thedude813
Yeah, I knew I wasn't being clear when I wrote it, but I tried the best I could. I said that "What I want is to be able to get the student pk, and the test pk of the latest date." This was a little ambiguous. Anyways, it doesn't matter, you got me close enough to the answer to get it all figured out. Thanks!
you're Welcome  |
|
|
|
|
|
|
|
|
Group by complications