Transact SQL ? - 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

SQL Server Development (2000)

Transact SQL ?

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2003-02-25 : 11:13:56

I have 2 tables. They have a relationship on one field. I want to select all the records in the first table with my unique records. I also want to select the top 16 from the 2nd table for each unique id. For example, table1 has 2 unique id's, 1234 and 4321. If 1234 and 4321 in table 1 both have 50 related records, i want to return the top 16 for 1234 and the top 16 for 4321, giving me 32 records total. Hope I didn't make this move confusing :)

Thanks in Advance,

Mike

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-25 : 11:30:31

select tbl2.id, tbl2.fld
from tbl1, tbl2
where tbl2.fld in (select top 16 t2.fld from tbl2 t2 where t2.id = tbl1.id order by t2.fld)
and tbl2.id = tbl1.id

==========================================
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.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2003-02-25 : 11:43:15

I didn't get the correct results w/ that syntax, here is my actual query: (i used 2 so the results were easier to read)

SELECT Student.StudentID,History.*

FROM Student,History

WHERE History.StudentID IN (SELECT Top 2 History.StudentID FROM History WHERE History.StudentID=Student.StudentID order by History.StudentID)
AND Student.StudentID=History.StudentID

ORDER BY History.StudentID

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-25 : 12:01:34

You need to use the pk on History

SELECT Student.StudentID,History.*
FROM Student,History
WHERE History.pk IN (SELECT Top 2 History.pk FROM History WHERE History.StudentID=Student.StudentID order by History.pk)
AND Student.StudentID=History.StudentID
ORDER BY History.StudentID

==========================================
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.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2003-02-25 : 12:13:27

that worked, thanks a bunch!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources