Min (pulling out only one record)

[sdempsey]

I have the following fields in my database

* Entry Number (unique for every record)
* Company ID
* Pat ID
* Date Seen
* Run Date

All fields can be the same i.e. Company ID, Pat ID, Date Seen and Run Date may be the only one that varies.Entry number will be different every time.

I would like to be able to get hold on the min run date entry number. Due to problems in the database outwith my control I need to have the Entry Number of the min run date. So cant do min(run date), pat id and company etc.

I started off my creating a temp table and created a unique field (Auto number) and order this by run date. I then tried taking the min(unique field) and orginal entry number.

However because the entry number is always different. I am pulling out all run dates and not just the entry number with the min run date.I cant do min of the orginal entry number as I dont know this will correspond to the min run date.


For example I have the following 3 records in the d/b.
yyyymmdd
Entry Number Company ID Pat ID Date Seen Run Date
23 a 1 20050609 20050409
198 a 1 20050609 20050309
789 a 1 20050609 20050909

I would like my query to return entry number 198.

Thanks

 

[bboffin]

Why don't you just do a query on the original table that sorts ascending on RunDate and returns only the first record. Then you can take the entry number from the record returned?

For example:

SELECT TOP 1 [Entry Number], RunDate FROM MyTable ORDER BY RunDate

Bob Boffin

 

[sdempsey]

The above situation may happen with a number of different rows. The above code would only pull out 1 row from the whole table?

So a pat_id may have 3 records or may just have 1. I would like 1 record returned for each pat
.

 

[Spenney]

How about a first query to return the minimum date for each pat_id - EG

SELECT pat_id, Min(rundate) AS MinOfrun_date
FROM tblYour_Table
GROUP BY pat_id;

Then using this query, join back to the original table to return the Entry Number ? (Use both fields in the query to join to their equivalent in the table)

Any good ?

 

[sdempsey]

Hi,

I have had a good look through the forum and the below seems to be the solution:-

Select FR.no, FR.date, FR.pat_id, FR.run_date, FR.entry_number
From #first_report as FR
Where run_date =
(Select MIN(run_date) FROM #first_report as FR2
Where FR2.pat_id = FR.pat_id and FR.no = FR2.no)This is your PK.

Thanks for your time.

 

[PHV]

And what about this ?
SELECT F.no, F.date, F.pat_id, F.run_date, F.entry_number
FROM [#first_report] As F INNER JOIN (
SELECT pat_id, Min(run_date) As MinDate FROM [#first_report] GROUP BY pat_id
) As G ON F.pat_id = G.pat_id AND F.run_date = G.MinDate

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