Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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
?
165 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

564 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