Selecting MAX date from a group of records

[pjewett]

Hi All,

New to the forum and to SQL.

I'm running a query to pull sales records for a specific customer that returns several dates (for each transaction). I only need the most recent date from the results however and am having difficulty figuring out how to get this.

Can anyone help?

Thanks

Current code is:

SELECT vw_Summary_sales_Cust_Region_Brick_All.ISBN13, Sum(vw_Summary_sales_Cust_Region_Brick_All.NET_U) AS 'Sum of NET_U', vw_Summary_sales_Cust_Region_Brick_All.DOC_DATE
FROM Staging_vista.dbo.vw_Summary_sales_Cust_Region_Brick_All vw_Summary_sales_Cust_Region_Brick_All
WHERE (vw_Summary_sales_Cust_Region_Brick_All.CUST='56879') AND (vw_Summary_sales_Cust_Region_Brick_All.PERIOD>='0502' And vw_Summary_sales_Cust_Region_Brick_All.PERIOD<='0911')
GROUP BY vw_Summary_sales_Cust_Region_Brick_All.ISBN13, vw_Summary_sales_Cust_Region_Brick_All.DOC_DATE

Resulting in:

9781579905507	Altered Art	6,996     11/17/2006
9781579905507	Altered Art	1,804     8/24/2006
9781579905507	Altered Art	1,408     8/1/2006
9781579905507	Altered Art	638       9/28/2006
9781579905507	Altered Art	154       7/1/2008
9781579905507	Altered Art	110       7/25/2008
9781579905507	Altered Art	6         9/26/2006
9781579905507	Altered Art	(2)       7/25/2007
9781579905507	Altered Art	(5)       2/16/2007
9781579905507	Altered Art	(8)       5/13/2008

These are individual transactions of purchases of one of our books. I need these individual transactions but in the end only need the most recent date (last time they purchased.)

[ck9663]

Could you post some sample data and your table structure?

--- CK

[pjewett]

I'm pulling the data from a view.

I've attached a sample record screenshot taken in Studio. Below is the sample I provided in the original post...is that not what you're looking for?

Thanks so much.


9781579905507 Altered Art 6,996 11/17/2006
9781579905507 Altered Art 1,804 8/24/2006
9781579905507 Altered Art 1,408 8/1/2006
9781579905507 Altered Art 638 9/28/2006
9781579905507 Altered Art 154 7/1/2008
9781579905507 Altered Art 110 7/25/2008 *
9781579905507 Altered Art 6 9/26/2006
9781579905507 Altered Art (2) 7/25/2007
9781579905507 Altered Art (5) 2/16/2007
9781579905507 Altered Art (8) 5/13/2008


So again I'm able to pull (via MS Query) total units sold of a given book title to a given customer lifetime. However I need to add to this the date of the last sale of that title to the customer. When I add the "docdate" (which is the order date) to the query I of course then get every individual sale of that specific title to the customer and the date of the sale. Our sales group only needs the most recent date though.

* is the only date I need but I need the units sold in all of those transactions so that I can provide TOTAL units sold and the date of the last sale.

[nbiswas]

Answer to Selecting MAX date from a group of records

Try this(a sample)

declare @t table(ISBN bigint,AlertNames varchar(50),FieldSums varchar(20),dt datetime)
insert into @t
select 9781579905507,'Altered Art','6,996','11/17/2006' union all
select 9781579905507,'Altered Art','1,804','8/24/2006' union all
select 9781579905507,'Altered Art', '1,408','8/1/2006'union all
select 9781579905507,'Altered Art','638','9/28/2006'union all
select 9781579905507,'Altered Art','154', '7/1/2008'union all
select 9781579905507,'Altered Art' , '110', '7/25/2008'union all
select 9781579905507,'Altered Art ','6','9/26/2006'union all
select 9781579905507,'Altered Art','(2)','7/25/2007'union all
select 9781579905507,'Altered Art','(5)' ,'2/16/2007'union all
select 9781579905507,'Altered Art ','(8)' ,'5/13/2008'

Query:

select isbn,MAX(dt) latestdate from @t
group by  isbn

Output:

isbn latestdate
9781579905507 2008-07-25 00:00:00.000

If you need some presentation, go ahead with union all like


select * from @t
union all
select null,'Latest Date : ',null,MAX(dt) from @t