General discussion


Picking 4 records per user

By jburk ·
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?


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

What about using the

by j.lupo In reply to Picking 4 records per use ...

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.

Collapse -

Having Clause

by jburk In reply to What about using the

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?

Collapse -

Depends on how you use it

by j.lupo In reply to Having Clause

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.

Collapse -


by jburk In reply to Depends on how you use it

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

Collapse -

Sorry about that

by j.lupo In reply to Thanks

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.

Related Discussions

Related Forums