Solved

How to select most recent record

Posted on 2010-11-12
29
444 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
[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
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

707 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