Solved

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

Posted on 2014-04-01
4
156 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 28

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 28

Accepted Solution

by:
sammySeltzer earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now