Solved

How to select most recent record

Posted on 2010-11-12
29
440 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
ID: 34126178
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
ID: 34126347
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
ID: 34127106
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 34127304
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
ID: 34127473
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
ID: 34127509
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
ID: 34127570
>> 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
ID: 34127709
Any comment on my comment ID:34127106?
0
 

Author Comment

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

Author Comment

by:fbc2010
ID: 34127825
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
ID: 34127860
thanks :) glad to have helped!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 34127868
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
ID: 34127914
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
ID: 34127924
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 34128020
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
ID: 34128210
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
ID: 34128246
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
ID: 34128272
Attached is the data and the expected result.
 ATTENDANCE.xlsx
0
 

Author Comment

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

Expert Comment

by:rockiroads
ID: 34128295
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
ID: 34128317
This returns 7 records?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34128411
ok, let me check your xls file
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34128537
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
ID: 34128540
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
ID: 34128557
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
ID: 34129078
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
ID: 34129216
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
ID: 34129478
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
ID: 34129797
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

856 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