Query subgroup help (or something like it)

[DrewBlue]

I am in desperate need for help with a query that has been taking too long to finish.

I have a specific set of data that is being grouped, but then I need to group it down to another level. The primary the columns to pay attention to are the OFFICEID, ID, and OFFICEREQ. I would like to return the first X (being the value shown in the OFFICEREQ field) users in each office. This list could easily grow to add more offices so it needs to be flexible and use the OFFICEREQ field to know how many will be selected.

I would very much like to keep this in one query to keep from getting to messy.

Does anyone have any solutions or simple queries I could try?

This is what the data looks like now.
OfficeID ID OfficeReq UserName Flag
8 89 3 XXXXX 1
8 245 3 XXXXX 1
8 319 3 XXXXX 1
8 257 3 XXXXX 1
8 88 3 XXXXX 1
8 306 3 XXXXX 1
8 254 3 XXXXX 1
8 271 3 XXXXX 1
8 248 3 XXXXX 1
9 179 5 XXXXX 1
9 173 5 XXXXX 1
9 62 5 XXXXX 1
9 115 5 XXXXX 1
9 64 5 XXXXX 1
9 134 5 XXXXX 1
9 60 5 XXXXX 1
9 73 5 XXXXX 1
9 74 5 XXXXX 1
9 75 5 XXXXX 1
9 162 5 XXXXX 1
9 71 5 XXXXX 1
9 148 5 XXXXX 1
9 142 5 XXXXX 1
9 176 5 XXXXX 1


The output would look exactly like this.
OfficeID ID OfficeReq Name Flag
8 89 3 XXXXX 1
8 245 3 XXXXX 1
8 319 3 XXXXX 1
9 179 5 XXXXX 1
9 173 5 XXXXX 1
9 62 5 XXXXX 1
9 115 5 XXXXX 1
9 64 5 XXXXX 1


Thank you very much in advance.

-Andrew

 

[r937]

first X" implies some type of sequence

your example shows the only other column that could possibly be used for sequencing is ID

however, the top 3 for OfficeID 8 are not the ones shown in your example

i'm not saying i can necessarily solve your problem but you would first have to define how to derive the correct sequence

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[DrewBlue]

I apologize for not being more clear. That was my problem with trying to describe this to a few of my friends, I just cant get it out right. Let me try to add more.

When I said “First X” I was implying that it will return a specified number of items from each group, that specific count is the number stored in OFFICEREQ. So for example, I may have 40 users in office #8 and 200 in office #9. That way, out of the table that stores all of the users data, I only return a short list of the data desired. Order or sequence of the results are not important at all, but to make sure that I get only 3 users (any of them, and any order) from office 8 and 5 users from office 9. Should I add another office (Office 10 for example) I may specify in the table that It return 13 of the 1000 users (again in any order). Therefore the query should be non-specific to any particular value or office number.

I hope this is better. If you will look at the main thread above, you will see the before and after examples of the output. The “before” table is one I copied directly from what I have now and modified the “After” table to show what I wanted to return when the new query is complete. You will notice that I only show 3 records from office 8 and 5 from office 9.

I really would appreciate any help you could provide. This is delaying my personal project quite a bit and now has become a personal vendetta to complete it correctly and not hack a way around it like I almost did last night.

 

[r937]

try this --

select t1.OfficeID
     , t1.ID
     , t1.OfficeReq
     , t1.Name
     , t1.Flag
  from yourtable t1
inner
  join yourtable t2
    on t1.OfficeID = t2.OfficeID
   and t1.ID <= t2.ID     
group 
    by t1.OfficeID
     , t1.ID
     , t1.OfficeReq
     , t1.Name
     , t1.Flag
having count(*) <= 
     ( select min(OfficeReq)
         from yourtable
        where OfficeID = t1.OfficeID )   
order 
    by t1.OfficeID
     , t1.ID

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[PHV]

SELECT A.OfficeID,A.ID,A.OfficeReq,A.UserName,A.Flag
FROM tblOffice A INNER JOIN tblOffice B ON A.OfficeID=B.OfficeID AND A.ID>=B.ID
GROUP BY A.OfficeID,A.ID,A.OfficeReq,A.UserName,A.Flag
HAVING Count(*)<=A.OfficeReq;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244