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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

How to select most recent record

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
fbc2010
Asked:
fbc2010
  • 10
  • 10
  • 6
  • +2
1 Solution
 
rockiroadsCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
Hi,

What about this.


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

Open in new window

0
 
hnasrCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Mark WillsTopic AdvisorCommented:
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
 
fbc2010Author Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
rockiroadsCommented:
>> 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
 
hnasrCommented:
Any comment on my comment ID:34127106?
0
 
fbc2010Author Commented:
Sage thanks for your input but I found it confusing to follow.
0
 
fbc2010Author Commented:
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
 
rockiroadsCommented:
thanks :) glad to have helped!
0
 
Mark WillsTopic AdvisorCommented:
No problems...

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

0
 
rockiroadsCommented:
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
 
fbc2010Author Commented:
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
 
rockiroadsCommented:
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
 
fbc2010Author Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
fbc2010Author Commented:
Attached is the data and the expected result.
 ATTENDANCE.xlsx
0
 
fbc2010Author Commented:
Thanks Mark, I want to stick with using the max function. Assume their is no ID column.
0
 
rockiroadsCommented:
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
 
fbc2010Author Commented:
This returns 7 records?
0
 
rockiroadsCommented:
ok, let me check your xls file
0
 
rockiroadsCommented:
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
 
rockiroadsCommented:
or one for display name and one for title (seperate checks - this is what I initially thought hence the union)
0
 
rockiroadsCommented:
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
 
fbc2010Author Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
fbc2010Author Commented:
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
 
Mark WillsTopic AdvisorCommented:
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 10
  • 10
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now