Most recent Record on a date field (1 Viewer)

[rkrause]

I am having trouble trying to get the most recent record. heres my query
i get no results on this query. Basically what i want is if CMTCH has 4 records over 1 year. I want the most recent record.
Example
10-1-12
8-1-12
2-15-12
5-25-12

I would want the records with the 10-1-12 date.

select
a.sumjobcode, a.sumEffDate, a.sumFinalPoints,
b.jobtitle, b.DSCRIPTN
FROM tblSummary a
left outer join vDepartments b
on a.sumJobCode = b.JOBTITLE
where a.sumEffDate =(
SELECT MAX(a.sumEffDate)
FROM tblsummary a)
and a.sumJobCode = 'CMTCH'

 

[mdlueck]

I do not quite follow the logic of your query.

To simply SELECT "Most recent Record on a date field" I would do something along the lines of...

SELECT TOP 1 ....
WHERE ...
ORDER BY [datecol] DESC

 

[rkrause]

what if my date i want isnt always the top row.

 

[rkrause]

Nevermind i get where you are coming from, i think that will work. i overlooked your order by.

 

[rkrause]

I tried using the max but i would get more then 1 result

 

[rkrause]

That is what i have, i get 4 records returned. All with 4 different dates, i want the most recent record.
select
a.sumjobcode, max(a.sumEffDate), a.sumFinalPoints,
b.jobtitle, b.DSCRIPTN
FROM tblSummary a
left outer join vDepartments b
on a.sumJobCode = b.JOBTITLE
where a.sumJobCode = 'CMTCH'
group by a.sumJobCode, a.sumEffDate, a.sumFinalPoints, b.JOBTITLE, b.DSCRIPTN

 

[rkrause]

can you display that as an example? im confused

 

[mdlueck]

Did my air code in #2 fail to deliver the desired results in some way?

 

[rkrause]

No that works, im just interested in the max function too.

 

[rkrause]

Im posting this in a sql forum, so im using SSMS not accesss...

 

[SQL_Hell]

Please use the example in post 2 by mdlueck, do not subquery in the same table using MAX, it's will be badly performing code compared to TOP 1.