How to select most recent record

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)

Hi,

What about this.

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

Select allOpen in new window

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

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

Select allOpen 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)

Select allOpen in new window

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

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)

Select allOpen in new window

Any comment on my comment ID:34127106?

Sage thanks for your input but I found it confusing to follow.

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

thanks :) glad to have helped!

No problems...

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

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.

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.

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

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])));
 

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);
 

Attached is the data and the expected result.
 ATTENDANCE.xlsx

Thanks Mark, I want to stick with using the max function. Assume their is no ID column.

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])

Select allOpen in new window

This returns 7 records?

ok, let me check your xls file

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?

or one for display name and one for title (seperate checks - this is what I initially thought hence the union)

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?

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);

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);

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.

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.