Group By Query

[GrommitAPW]

I am converting some Access queries to be 'pass through' to a SQL Server backend. In Access there is a LAST function when running a GROUP BY query - is there an equivalent in SQL Server ?

eg table below has columns Type, Quantity & Date. I would like to group by Type, return the latest Date & return the corresponding Quantity value.

Type Quantity Date
a 5 15 Jan 2001
a 2 20 Jan 2001

Many Thanks in advance,
Adrian

 

[cdukes]

The following shold work:

SELECT MAX(Date), Quantity
from tbl
group by Type

Chris Dukes

 

[jballum]

select
A.type,
A.quantity,
A.date
from
(select
distinct type, quantity, date
FROM
tableName) A
inner join
(select
type, max(date)
FROM
tableName
group by
type) B
on A.type = B.type and
A.date = B.date

This might give you some ideas.

JB

 

[GrommitAPW]

Thanks for the tip.

When I tried it I got the following error message

Column 'tbl.Quantity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any ideas ?
Thanks for your time, Adrian

 

[GrommitAPW]

Thanks JB - I had a feeling that this approach be a way forward.