Discussion Locked

Picking 4 records per user

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a large database and I need to sample records from it based upon the user ID in each record. The problem is I need four records for each user. I can ge the whole list for all users. I can get four records for one user btu I cannot get 4 records for all users. The only solution I have come up with is below:

SELECT TOP 4 tblMatchedVolume.Underwriter_ID, tblMatchedVolume.Loan_Type_Code, tblMatchedVolume.Mortgage_Loan_No___10_Character, Date() AS [Review Month]
FROM tblMatchedVolume INNER JOIN _TblActiveUW ON tblMatchedVolume.Underwriter_ID = [_TblActiveUW].OPID
WHERE (((tblMatchedVolume.Underwriter_ID)=[Enter ID]));

I have approx. 273 users and I don;t want to run this 273 times. Any ideas?

Thanks.

5 replies

by j.lupo

having clause?

having count(Underwriter_id) < 5

Just a thought. You may need to look up the syntax, but I believe that would solve your issue.

by jburk

Wouldn't I need to have an aggregate to use Having count(Underwriter_ID)< 5? Wpouldn;t that just give me all the people who had less than five records in the table and exclude evryone with more?

by j.lupo

Unelss you want all the data for those four records, I have done this before. Of course I didn't need all the data, so it was easier. You could also do a nested join and pull it that way. Something like

select * from tab1 t
where t.col = (select col from tab1 t2
where t2.col = t.col
and rownum < 5)

or something like that anyway. I am doing this off the top of my head - always a bad idea. At any rate, there are a couple of choices.

Hope this helps.

by jburk

I'll give it a try. What exactly do you mean by tab1 t? Table 1? What is t?

by j.lupo

I am from the Oracle world. I was simply creating an example with fictional tables and table alias'

tab1 was the name of the actual table in the DB. I aliased it to "t". I did the same with the sub select only the tab1 table was aliased to t2 this time to make them unique entities.

This discussion is closed to new replies.