Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
162 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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