Link to home
Start Free TrialLog in
Avatar of IntercareSupport
IntercareSupport

asked on

How do i get the first, last, or top 10 records for each one?

hey ee!
I'm trying to get 10 records for each PayTo_Doctor_Id from this query.

SELECT Location, PayTo_Doctor_Id, CPT_Code, Encounter_Det.Patient_Id, Patient_Chart,
Patient_Last_Name, Patient_First_Name from Encounter_Det INNER JOIN Patient_Personal
ON Encounter_Det.Patient_Id = Patient_Personal.Patient_Id
ORDER BY PayTo_Doctor_Id

This query spits out a few thousand records, but I just want 10 for each PayTo_Doctor_Id. Any ideas?  
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

a random 10? last 10?
SELECT Location, PayTo_Doctor_Id, CPT_Code, Encounter_Det.Patient_Id, Patient_Chart,
Patient_Last_Name, Patient_First_Name from Encounter_Det INNER JOIN Patient_Personal
ON Encounter_Det.Patient_Id = Patient_Personal.Patient_Id
WHERE ((([PayTo_Doctor_Id] Mod [10])=0))
ORDER BY PayTo_Doctor_Id
Wait...let me work on this a bit more. You'll need to group by the PayTo_Doctor_ID then select the top 10 for each Doc. Ignore my previous.
Avatar of IntercareSupport
IntercareSupport

ASKER

wow random sounds cool.. is there a big difference in syntax between random and last?
random would rely on a count in this case. For example, if I wanted to pull 5 random numbers out of a set of 100 records, then I would have to know that there were 100 records, otherwise since I'd have to match an ID to one of the 5 random numbers, and it arbitrarily pulls 5 random numbers out of a 1000, then chances are, I won't get what I want.

Theory wise...we're going to have to group the records you're pulling now. That's where I'm trying to work it out. I have to isolate one DR's records, so I can pull just 10 of them. So it's kind of sticky when you're trying to do it in a query.
J.
I'm struggling with trying to get this into a SQL statement. How about a code solution? Have you gotten into using VBA yet?
Read through this. It pulls a random 5 records for each state and puts them into a temp table.

We could modify it to pull a random 10 for each doctor and put it in a temp table.  Let me know what you think.

Function RandomFiveByState()
Dim rsSt As DAO.Recordset
Dim rsCus As DAO.Recordset
Dim rsTemp As DAO.Recordset
Dim sqlSt As String
Dim sqlCus As String
Dim varBookmark As Variant
Dim strCommand As String
Dim lngMove As Long
Dim I As Long
sqlSt = "Select * from tblStates;"
Set rsSt = CurrentDb.OpenRecordset(sqlSt)
rsSt.MoveFirst
Do Until rsSt.EOF
    sqlCus = "select * from MyCustomer where State = " & rsSt!sta_ID & ";"
    Set rsCus = CurrentDb.OpenRecordset(sqlCus)
    If rsCus.EOF Then GoTo NextState
    rsCus.MoveLast
    MyCount = rsCus.RecordCount
    rsCus.MoveFirst
    Set rsTemp = CurrentDb.OpenRecordset("Select * from myFive;")
        For I = 1 To 5
            myran = Int((MyCount - 1 + 1) * Rnd + 1)
            rsCus.Move myran
            rsTemp.Addnew
            rsTemp![Last Name] = rsCus![Last Name]
            rsTemp![First Name] = rsCus![First Name]
            rsTemp![Address] = rsCus![Address]
            rsTemp![State] = rsCus![State]
            rsTemp.Update
            'collect customer information here
            rsCus.MoveFirst
        Next I
    rsCus.Close
    Set rsCus = Nothing
NextState:
rsSt.MoveNext
Loop
rsSt.Close
Set rsSt = Nothing
End Function
I've just been going through query analyzer. I'm running reports from there right now so I can get them done. I haven't come close to building anything for it yet.
Would you like to? :o) we can make you into a coder
yeah sounds good. What now?
First...Microsoft is a great resource for finding answers and alternative solutions. Since you've not really coded yet..

read this article

http://support.microsoft.com/kb/208822

it states:
This article demonstrates how you can create a report that ranks a specific number of records for each group in order according to a top value, for example, the top five selling products per category.

Sounds kind of like you.
Avatar of Anthony Perkins
Try something like this:

SELECT      Location,
      PayTo_Doctor_Id,
      CPT_Code,
      e.Patient_Id,
      Patient_Chart,
      Patient_Last_Name,
      Patient_First_Name
From      Encounter_Det e
      INNER JOIN Patient_Personal p ON e.Patient_Id = p.Patient_Id
Where      PayTo_Doctor_Id  In (
                  Select      Top 10
                        PayTo_Doctor_Id
                  From      Encounter_Det e
                        Inner Join Patient_Personal p ON e.Patient_Id = p.Patient_Id

                  Order By
                        NEWID())

Caveat:  I do not know the structure of your tables so that query may run extremely slow.
Let's try that again:

SELECT      Location,
      PayTo_Doctor_Id,
      CPT_Code,
      e.Patient_Id,
      Patient_Chart,
      Patient_Last_Name,
      Patient_First_Name
From      Encounter_Det e
      INNER JOIN Patient_Personal p ON e.Patient_Id = p.Patient_Id
Where      PayTo_Doctor_Id  In (
            Select      Top 10
                  PayTo_Doctor_Id
            From      Encounter_Det e
                  Inner Join Patient_Personal p ON e.Patient_Id = p.Patient_Id
            Group By
                  PayTo_Doctor_Id
            Order By
                  NEWID())
Hi acperkins,
This looks great...
Where does the NEWID come from?
>>This looks great...<<
That may be so.  Whether it works is another matter. :)

>>Where does the NEWID come from? <<
It is a T-SQL function used to create a GUID and as such can be used to randomly order the rows.
The last sql statement is great, but it gives me the top ten providers. I'm trying to get 10 records for each provider. It could be the last, first, random, or anything of that sort. I think we have about 60 providers, so my ideal number would be 600 records returned.
Let's revisit the code sample I put above. See my comments below.

Function RandomFiveByState()
Dim rsSt As DAO.Recordset
Dim rsCus As DAO.Recordset
Dim rsTemp As DAO.Recordset
Dim sqlSt As String
Dim sqlCus As String
Dim varBookmark As Variant
Dim strCommand As String
Dim lngMove As Long
Dim I As Long
sqlSt = "Select * from tblStates;"                  '<--this would be to get a distinct list of Dr's
Set rsSt = CurrentDb.OpenRecordset(sqlSt)
rsSt.MoveFirst
Do Until rsSt.EOF
    sqlCus = "select * from MyCustomer where State = " & rsSt!sta_ID & ";" '<----This one would be to grab all the records for the first Dr.
    Set rsCus = CurrentDb.OpenRecordset(sqlCus)
    If rsCus.EOF Then GoTo NextState
    rsCus.MoveLast
    MyCount = rsCus.RecordCount
    rsCus.MoveFirst
    Set rsTemp = CurrentDb.OpenRecordset("Select * from myFive;")  '<--   This would Open a temp table that you'd create to hold the output (the 10 records per doctor)
        For I = 1 To 5                                                     '<---------You would change this number to 10
            myran = Int((MyCount - 1 + 1) * Rnd + 1)  '<---Here's where we grab the random record
            rsCus.Move myran
            rsTemp.Addnew
            rsTemp![Last Name] = rsCus![Last Name]  <----Starting here, we would add your fields
            rsTemp![First Name] = rsCus![First Name]
            rsTemp![Address] = rsCus![Address]
            rsTemp![State] = rsCus![State]
            rsTemp.Update
            'collect customer information here
            rsCus.MoveFirst
        Next I
    rsCus.Close
    Set rsCus = Nothing
NextState:
rsSt.MoveNext
Loop
rsSt.Close
Set rsSt = Nothing
End Function



As you can see that's not a LOT to change to get this going. If you copy it and paste it into a CODE module in your database, then we're halfway there.

You'll need to create an empty table to accept the results. You would need to have the fields as defined by your query.

Then you would need a way to launch it. Maybe a button on a form or something.

J
Should I copy this code into a Access db even though I'm working in Query Analyzer/SQL database? I don't see any connection strings.  I've already manually deleted the extra records from the requested report because of priorities, but I would still like to learn how to get 10 records for each group(provider). Is their no possiblity doing it from query analzer with a strait sql statement? I do plan on making a little app for this, but i'll save some questions for that =).
I think Anthony was almost there - except I don't think attempting a random selection is a good goal for you (due to the potentially varying nature of the subquery, you might end up with more or less than a Top 10), and if the following doesn't work you could perhaps do with describing which fields belong to which table - and the purpose of each

SELECT      Location,
      PayTo_Doctor_Id,
      CPT_Code,
      e.Patient_Id,
      Patient_Chart,
      Patient_Last_Name,
      Patient_First_Name
From      Encounter_Det e
      INNER JOIN Patient_Personal p ON e.Patient_Id = p.Patient_Id
Where      e.Patient_Id  In (
            Select      Top 10
                  e2.Patient_Id
            From      Encounter_Det e2
            Where e2.PayTo_Doctor_Id = e.PayTo_Doctor_Id
            Order By
                  e2.Patient_Id DESC)
its working great with an exception of a couple providers that are only returning 1 or 2 records. I know they have a few thousand records inder their name. Can you explain what the syntax is doing? I don't under stand this part.

e2.Patient_Id From  Encounter_Det e2 Where e2.PayTo_Doctor_Id = e.PayTo_Doctor_Id
did i modify this correctly?

SELECT      
      Location,
      Dept_Code,
      PayTo_Doctor_Id,
      CPT_Code,
      e.Patient_Id,
      Patient_Chart,
      Patient_Last_Name,
      Patient_First_Name
From  
      Encounter_Det e
INNER JOIN
      Patient_Personal p ON e.Patient_Id = p.Patient_Id
Where      
      e.Patient_Id In (Select Top 10 e2.Patient_Id From Encounter_Det e2
      Where e2.PayTo_Doctor_Id = e.PayTo_Doctor_Id
      Order By e2.Patient_Id DESC)
and
      Encounter_Date Between '2007-01-01 00:00:00.000' and getDate()
and
      Dept_Code = 'ME'
Order By
      PayTo_Doctor_Id
Never did spot that this had continued.
What was your intent of modifying?  Just to your object names?
Did it execute then?
It executed, but some providers came up with only a few records and when i run a count they a few thousand
How do you mean "some providers"?
Are you using this against different databases?  Using different drivers to access the data?

Is it working in some instances (i.e. returning thousands of rows) but not in others?

(I'm assuming the difference between a few and thousands can't be apportioned to anything like ties?)
If i do a basic sql statement
SELECT  Location, Dept_Code, PayTo_Doctor_Id,  CPT_Code,  e.Patient_Id,  
Patient_Chart, Patient_Last_Name, Patient_First_Name
From  Encounter_Det e INNER JOIN Patient_Personal p ON e.Patient_Id = p.Patient_Id

There are a few thousand records returned for each provider, but when i do the sql statement you provided it only returns a couple records for some providers, Most of the other provider have around 10.
I'm using the same db through query analyzer.


What about the other critieria you specified on it

SELECT  
    Location, Dept_Code, PayTo_Doctor_Id,  CPT_Code,  e.Patient_Id,
    Patient_Chart, Patient_Last_Name, Patient_First_Name
FROM
    Encounter_Det e INNER JOIN Patient_Personal p ON e.Patient_Id = p.Patient_Id
WHERE
    Encounter_Date Between '2007-01-01 00:00:00.000' and getDate()
AND
    Dept_Code = 'ME'

How many does that return?
I added that to specifically get medical providers and 1st quarter data. Either way It should return a lot of data back.
ASKER CERTIFIED SOLUTION
Avatar of IntercareSupport
IntercareSupport

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial