How to select TOP X results of a field for each value of another field?

[Michael R]

Hello!

There's something that seems quite simple to do, but I still don't understand how to do it:

I need to create a query from the following table:

Table1:

P: Date: CountOfMeetings:
1 01/12 4
1 02/12 0
1 03/12 2
1 04/12 0
1 05/12 3
1 06/12 2
1 07/12 2
2 01/12 3
2 02/12 2
2 07/12 3
2 12/12 1
2 14/12 3
2 15/12 4
3 3/12 2
3 4/12 4
3 5/12 0
3 9/12 2
3 10/12 6
3 11/12 5

The query should take TOP 3 Dates for each P(person), so the result should be like the following:

P: Date: CountOfMeetings:
1 01/12 4
1 02/12 0
1 03/12 2
2 01/12 3
2 02/12 2
2 07/12 3
3 3/12 2
3 4/12 4
3 5/12 0

(The CountOfMeetings field is just an example field)

I appriciate your time.

[Rabbit]

You'll need to use a subquery. But even before you can use a subquery, you need to fix your data to support it. First off, you need a unique key of some sort, even if it's an autonumber. Second, your "date" field isn't a date, I can't figure out what format it is. Third, your "top 3" isn't top 3 of anything, it sounds more like you're looking for first 3 rather than top 3.

[Michael R]

I have a unique key, my date is a real date, I just gave a simplified example in order not to overload with too much detail.
I need the 3 lowest dates.

How can I use the subquery in order to get the result?

[Rabbit]

What you do is alias the table so that you can access it from the subquery. The subquery just needs to return the top 3 IDs sorted by your date so you cam select those IDs from the main query.

[NeoPa]

Michael R:
I just gave a simplified example in order not to overload with too much detail.

We applaud you for your thinking, and for a question where obvious attempts have been made to pass the required info. Please bear in mind for future questions though, that short/unclear date information leads to ambiguity and potential confusion. Not a criticism, as clearly you've made efforts to be clear, but worth bearing in mind certainly.

Rabbit. I'm really rusty on this point (and I seem to recall it was you who first led me to understand some of the possibilities available for use in SQL subqueries). I tried out the SQL below and it prompted for tO.P on line #13. I've used slightly different field names than the question, but I think it's clear what does what. Would you possibly know where I've gone wrong here?

SELECT DISTINCT
       tO.P
     , sQ.Start
     , sQ.CVal
FROM   [Tbl1] AS tO
       INNER JOIN
       (
    SELECT TOP 3
             [P]
           , [Start]
           , [CVal]
    FROM     [tbl1] AS tI
    WHERE    (tI.P=tO.P)
    ORDER BY [Start]
       ) AS sQ
  ON   tO.P=sQ.P

[NeoPa]

Just to clarify my thinking here:

I believe subqueries in the FROM clause are only determined once. As such it doesn't make sense to me that they can take record-level parameters (tO.P changes for each record in tO ==> the subquery will not have access to these values, and couldn't be run again to produce different results on every change of tO.P anyway).

I cannot see a way for this to work with subqueries outside of the FROM clause. Here I'm on shaky ground as I know you've put forward solutions in this area before that I hadn't thought of. For now at least though, it seems to me this is not possible.

[Rabbit]

That's probably why it's prompting for it. Because it's in the FROM clause. However, it may also be that the word TO is a reserved SQL key word.

However, I don't believe that SQL would return the results intended. Your subquery will only return the top 3 rather than top 3 per group even if it wasn't in the FROM clause.

There's two queries that can get the requested results.

SELECT GroupField, SortField, ValueField
FROM Table1 Tout
WHERE UniqueID IN (
    SELECT TOP 3 UniqueID
    FROM Table1 Tin
    WHERE Tin.GroupField = Tout.GroupField
    ORDER BY SortField
)
 
SELECT TP.GroupField, TP.SortField, TP.ValueField
FROM Table1 TP
INNER JOIN Table1 TC ON TP.GroupField = TC.GroupField
    AND TC.SortField <= TP.SortField
GROUP BY TP.GroupField, TP.SortField, TP.ValueField
HAVING COUNT(*) <= 3

I suspect, given proper indexes, the second query will run faster. But the first one is easier to understand.

[NeoPa]

Impressed.
-NeoPa :-)

PS: It never occurred to me that tableOutside (or tO) might clash with TO :-D
I was getting reasonably inventive with my SQL for a while, but clearly I'm rusty. This is good stuff though. I particularly appreciate the second version. Less obvious (a bonus in itself) but also more efficient logically (even if, as you say, it's a little less intuitive to follow).

[Rabbit]

The second query is a relatively new discovery. I found it while researching how to store dynamic hierarchies in databases. It was used to determine the depth of the nodes in the hierarchy but I've found it useful in other situations, especially when speed is a concern.

[NeoPa]

Nice :-)

All we need now is to check that Michael can actually appreciate the concepts involved and reproduce this in his quesry. Let us know Michael.

[ADezii]

@Michael R - A Code Based solution should be relatively simple, I'll see what I come up with. At least it will give you another Option to work with.

[ADezii]

The following Code will produce the exact results that you are looking for, then write them to the Immediate Window. It does not require a Unique Key/Index, and will produce the three lowest Dates and Meetings Value for each Unique Value of P. If the number of Records for a given P are less than three, it will display them also, but no Records greater than three. First, a couple of assumptions:

1. Table Name: tblTest
2. Field Names/ Data Types:
   1. [P] {LONG}
   2. [Date] {DATE/TIME}
   3. [Meetings] {LONG}

   Dim strSQL1 As String
   Dim strSQL2 As String
   Dim MyDB As DAO.Database
   Dim rstUnique As DAO.Recordset          'Unique Values for P
   Dim rst As DAO.Recordset
   Dim intCtr As Integer
    
   strSQL1 = "SELECT DISTINCT [P] FROM tblTest ORDER BY [P];"
    
   Set MyDB = CurrentDb
   Set rstUnique = MyDB.OpenRecordset(strSQL1, dbOpenDynaset, dbOpenForwardOnly)
    
   Debug.Print "---------------------------------------------"
   Debug.Print "P:", "Date:", "Meetings"
   Debug.Print "---------------------------------------------"
    
   With rstUnique
     Do While Not .EOF
       strSQL2 = "SELECT * FROM tblTest WHERE [P] = " & ![P] & " ORDER BY [Date];"
         Set rst = MyDB.OpenRecordset(strSQL2, dbOpenSnapshot)
           Do While Not rst.EOF
             intCtr = intCtr + 1
               Debug.Print ![P], rst![Date], rst!Meetings
                 If intCtr = 3 Then Exit Do
                   rst.MoveNext
           Loop
             intCtr = 0        'RESET
             rst.MoveFirst
         .MoveNext
     Loop
   End With
    
   rstUnique.Close
   rst.Close
   Set rstUnique = Nothing
   Set rst = Nothing

3. OUTPUT based on Sample Data:
   

   ---------------------------------------------
   P:            Date:         Meetings
   ---------------------------------------------
    1            12/1/2011      4 
    1            12/2/2011      0 
    1            12/3/2011      2 
    2            12/1/2011      3 
    2            12/2/2011      2 
    2            12/7/2011      3 
    3            12/3/2011      2 
    3            12/4/2011      4 
    3            12/5/2011      7 
    4            1/23/2011      10 
    4            9/16/2011      12 
    5            5/30/2011      9 

P.S. - If you wish to take this approach, it will be a simple matter to write the Results to a Table for viewing, just let me know.