Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Finding the oldest record in a group of records; some kind of looping through records?

Posted on 2014-04-01
4
Medium Priority
?
164 Views
Last Modified: 2014-04-01
I have a table of data across a large group of people for charting events distinguished by these fields:

Earliest Date / Time image
as you can see any given PAT_NUM can have multiple entries against a PMP (order number) which vary in date and time.

What I'm after is a query that will work out the 1st (oldest) entry for a given PAT_NUM for a given PMP.

All fields are stored as text.
0
Comment
Question by:ghettocounselor
  • 2
  • 2
4 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 39969690
I think you can do this in query.

SELEcT MAX(pat_num),... from your table

OR

SELECT * from yourtable ORDER BY pat_num ASC (ascending) displays from oldest to newest.

If you just wish to display ONE record, then use SELECT TOP 1 and Order by pat_num ASC
0
 

Author Comment

by:ghettocounselor
ID: 39969716
sorry, more clarity: I need the 1st (oldest) entry for 'each' PAT_NUM for 'each' PMP, as example: rather than the 4 entries i have above for the pat num ending in 4631I'm wanting only the oldest entry (by date and time, recognizing that there could be two entries on 1 day). The table has 100's of distinct pat_num's each of which will have 1 distinquished row of data in the end representing the oldest entry for each PMP number.
0
 
LVL 29

Accepted Solution

by:
sammySeltzer earned 2000 total points
ID: 39969777
Oh, Ok.

You can try this:

;With CTE 
As
(
 Select  *  ,ROW_NUMBER() Over (Partition By Pat_num, pmp, trans_type, trans_type_desc Order By Pat_num, charted_date, charted_time ) As UN 
From  @yourTable 
)

Select Pat_num, pmp, trans_type, trans_type_desc, charted_date, charted_time From CTE Where UN = 1 Order By Pat_num

Open in new window

0
 

Author Closing Comment

by:ghettocounselor
ID: 39970010
SUPERB!

Thanks for the quick back and forth to work this out.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question