Solved

How to select most recent record

Posted on 2010-11-12
29
436 Views
Last Modified: 2012-05-10
I have data that looks like this.

Paydate                Name    Week   Reason

11/01/10 2:55pm   Bill          2          Sick
11/01/10 3:55pm   Bill          2           Vac
11/01/10 1:00pm   Bill          2           Vac
11/01/10 2:00pm   John        2           Sick
11/02/10 1:30pm   John        2            Vac

I need a select query which give me only the most recent record for each name.

Appreciate some assistance.
0
Comment
Question by:fbc2010
  • 10
  • 10
  • 6
  • +2
29 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
how about something like this

select name, paydate, week, reason
from mytable as a
where paydate in (select max(paydate) from mytable where a.name = name)

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
Hi,

What about this.


select name, max(paydate)paydate, week, reason
from mytable
group by
name, week, reason

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Compare with this:
Table a(f1, f2, f3)
f1      f2      f3
2      2      1
1      1      2
1      2      3
2      1      4
Process: recent record with higher f2

SELECT x.f1, x.f2, y.f3
FROM (SELECT a.f1, Max(a.f2) AS f2
FROM a
GROUP BY a.f1)  AS x INNER JOIN a AS y ON (x.f2 = y.f2) AND (x.f1 = y.f1)

Result:
f1      f2      f3
1      2      3
2      2      1
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
If the only thing (by name) to resolve the desired row is max(paydate) then that is the critical piece of information to match on.

Rockiroads posting will do that for by resolving the where clause...

You can also do that in a join rather than inline query which *might* operate more efficiently given a largish database...

Select T1.* 
from table1 T1
inner join (select name,max(paydate) as Max_paydate from table1 group by name) T2 on T2.name = T1.name and T2.max_paydate = T1.paydate

Open in new window


Another common approach is to use the EXISTS clause

Select T1.* 
from table1 T1
where not exists (select 1 from table1 T2 where T2.name = T1.name and T2.paydate > T1.paydate)

Open in new window


0
 

Author Comment

by:fbc2010
Comment Utility
So, I followed ROCKIROAD's solution and ended with this query

SELECT T1.Title, T1.[Display Name], T1.WK1_MON_HRS, T1.WK1_MON_REASON, T1.WK1_TUES_HRS, T1.WK1_TUES_REASON, T1.WK1_WED_HRS, T1.WK1_WED_REASON, T1.SUBMITTED_DATE
FROM T1 as NEWT1
where T1.SUBMITTED_DATE in (select max(T1.SUBMITTED_DATE) from T1 where NEWT1.[Display Name] = T1.[Display Name]);

But when I run it, it prompts on T1.Title
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Yes, because you gave the table T1 an alias of NEWT1.


Given the above... Try:

Select NewT1.* 
from T1 as NewT1
where not exists (select 1 from T1 AltT1 where AltT1.[Display Name] = NewT1.[Display Name] and AltT1.submitted_date > NewT1.submitted_date)

Open in new window

0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
>> But when I run it, it prompts on T1.Title

mark already said but let me try explain

we have to use a table alias because of the inner query. the inner query gets the max date for the specified name in the outer query and because the tables are the same name we therefore have to use the alias


SELECT NEWT1.Title, NEWT1.[Display Name], NEWT1.WK1_MON_HRS, NEWT1.WK1_MON_REASON, NEWT1.WK1_TUES_HRS, NEWT1.WK1_TUES_REASON, NEWT1.WK1_WED_HRS, NEWT1.WK1_WED_REASON, NEWT1.SUBMITTED_DATE
FROM T1 as NEWT1
where NEWT1.SUBMITTED_DATE in (select max(T1.SUBMITTED_DATE) from T1 where NEWT1.[Display Name] = T1.[Display Name]);
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Any comment on my comment ID:34127106?
0
 

Author Comment

by:fbc2010
Comment Utility
Sage thanks for your input but I found it confusing to follow.
0
 

Author Comment

by:fbc2010
Comment Utility
Mark, thanks for your solution it produces the correct result but Rockiroads solution was a little more logical for me to understand and follow given I'm a novice to SQL.  Thanks
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
thanks :) glad to have helped!
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
No problems...

Both my queries would have (typed up a small test database to check). Always very happy to explain and to educate.

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
fbc2010, sometimes it is good to ask for an explanation on how it works on a working solution, if you do not understand it. Good way to learn :) you can also split points then.
0
 

Author Comment

by:fbc2010
Comment Utility
Rockirods, yes an explanation is helpful and yours helped a lot.  Any good books you can recommend that would explain queries at this level.  The beginning tutorial stuff doesn't get into stuff like this.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
No probs. Since the sql from mark worked, I was more talking about that since you said you did not quite understand it.

w3schools is an excellent starting point - have a look here http://www.w3schools.com/sql/sql_intro.asp

In terms of books I do not know of any as everything I know about sql has been self taught

0
 

Author Comment

by:fbc2010
Comment Utility
Rockirods

Realized I need a little more help on this query.

I actually need to select the latest record for both name A2.[Display Name] and Title.
A2.Title (which is actually another date which represents the payroll start date).

So employees submit their data multiple times for the same payroll start date and I need to find the latest record based on A2.SUBMITTED_DATE for that particular payroll start date A2.Title.  

Sorry for the somewhat confusing field names.  The actual query and data can be found below. I think I need a group by clause but can't get it to work.

A snapshot of the data is attached.  The end result should be 4 records - 2 records for each employee

SELECT A2.Title, A2.[Display Name], A2.SUBMITTED_DATE, A2.WK1_MON_HRS, A2.WK1_MON_REASON, A2.WK1_TUES_HRS, A2.WK1_TUES_REASON, A2.WK1_WED_HRS, A2.WK1_WED_REASON, A2.WK1_THUR_HRS, A2.WK1_THUR_REASON, A2.WK1_FRI_HRS, A2.WK1_FRI_REASON, A2.WK2_MON_HRS, A2.WK2_MON_REASON, A2.Wk2_TUES_HRS, A2.WK2_TUES_REASON, A2.WK2_WED_HRS, A2.WK2_WED_REASON, A2.WK2_THUR_HRS, A2.WK2_THUR_REASON, A2.WK2_FRI_HRS, A2.Wk2_FRI_REASON, A2.OTH_REAS_1, A2.OTH_REAS_2, A2.COMMENT_1
FROM ATTENDANCE AS A2
WHERE (((A2.SUBMITTED_DATE) In (select max(ATTENDANCE.SUBMITTED_DATE) from ATTENDANCE where A2.[Display Name] = ATTENDANCE.[Display Name])));
 Data Snapashot
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Try changing your where clause...


WHERE A2.ID = (select top 1 ID from ATTENDANCE where A2.[Display Name] = ATTENDANCE.[Display Name] and A2.[Title] = ATTENDANCE.[Title] order by submitted_date desc);
 
0
 

Author Comment

by:fbc2010
Comment Utility
Attached is the data and the expected result.
 ATTENDANCE.xlsx
0
 

Author Comment

by:fbc2010
Comment Utility
Thanks Mark, I want to stick with using the max function. Assume their is no ID column.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
2 records per employee right? You want the max paydate and max title?

so how about using a union



SELECT A2.Title, A2.[Display Name], A2.SUBMITTED_DATE, A2.WK1_MON_HRS, A2.WK1_MON_REASON, A2.WK1_TUES_HRS, A2.WK1_TUES_REASON, A2.WK1_WED_HRS, A2.WK1_WED_REASON, A2.WK1_THUR_HRS, A2.WK1_THUR_REASON, A2.WK1_FRI_HRS, A2.WK1_FRI_REASON, A2.WK2_MON_HRS, A2.WK2_MON_REASON, A2.Wk2_TUES_HRS, A2.WK2_TUES_REASON, A2.WK2_WED_HRS, A2.WK2_WED_REASON, A2.WK2_THUR_HRS, A2.WK2_THUR_REASON, A2.WK2_FRI_HRS, A2.Wk2_FRI_REASON, A2.OTH_REAS_1, A2.OTH_REAS_2, A2.COMMENT_1
FROM ATTENDANCE AS A2
WHERE A2.SUBMITTED_DATE In (select max(ATTENDANCE.SUBMITTED_DATE) from ATTENDANCE where A2.[Display Name] = ATTENDANCE.[Display Name])
UNION ALL
SELECT A2.Title, A2.[Display Name], A2.SUBMITTED_DATE, A2.WK1_MON_HRS, A2.WK1_MON_REASON, A2.WK1_TUES_HRS, A2.WK1_TUES_REASON, A2.WK1_WED_HRS, A2.WK1_WED_REASON, A2.WK1_THUR_HRS, A2.WK1_THUR_REASON, A2.WK1_FRI_HRS, A2.WK1_FRI_REASON, A2.WK2_MON_HRS, A2.WK2_MON_REASON, A2.Wk2_TUES_HRS, A2.WK2_TUES_REASON, A2.WK2_WED_HRS, A2.WK2_WED_REASON, A2.WK2_THUR_HRS, A2.WK2_THUR_REASON, A2.WK2_FRI_HRS, A2.Wk2_FRI_REASON, A2.OTH_REAS_1, A2.OTH_REAS_2, A2.COMMENT_1
FROM ATTENDANCE AS A2
WHERE A2.TITLE In (select max(ATTENDANCE.TITLE) from ATTENDANCE where A2.[Display Name] = ATTENDANCE.[Display Name])

Open in new window

0
 

Author Comment

by:fbc2010
Comment Utility
This returns 7 records?
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, let me check your xls file
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Can I just get some clarification here

You definitely have an ID field now and this is an autonumber? I did not realise you had one if that is the case. I just noticed it in your xls file.

When you want the most recent record, you wanted 2 per employee. So is this based on both the display name and title?
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
or one for display name and one for title (seperate checks - this is what I initially thought hence the union)
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Ok, ID is not what we need to look on in terms of recent record right?. your definition of recent record is the highest date?
0
 

Author Comment

by:fbc2010
Comment Utility
Need newest record (based on Submiitied Date) for Display Name (Name) and Title (Pay Period Date)

Hope that clears things up a bit.

BTW, this solution from Mark Willis returned the correct result but I need something that doesn't use ID.

WHERE A2.ID = (select top 1 ID from ATTENDANCE where A2.[Display Name] = ATTENDANCE.[Display Name] and A2.[Title] = ATTENDANCE.[Title] order by submitted_date desc);
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Why not use ID - it positively identifies the individual rows needed... But anyway, could also try:

WHERE A2.Submitted_Date = (select top 1 Submitted_Date from ATTENDANCE where A2.[Display Name] = ATTENDANCE.[Display Name] and A2.[Title] = ATTENDANCE.[Title] order by submitted_date desc);
0
 

Author Comment

by:fbc2010
Comment Utility
Mark, that last query doesn't return the correct result.  Using ID definately returns the correct result.  Only reason I was looking for a solution with ID is I have another database where I need to so something similar but there is no ID field in this case.
Thanks for your time and help on this.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Well, it really depends on how much criteria we can use to highlight the most appropriate selections.

Chances are the general approach above (sometimes better use the one with "exists" because you can add heaps of "qualifiers" in there).

But, whenever there is a unique ID then it is always best to use that.

If there isnt, then there are methods available, but it all depends on both the requirement and what the data allows us to do.

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

771 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

10 Experts available now in Live!

Get 1:1 Help Now