SQL Help

A[L]C · 17 Mar 2010 at 14:53

Hi all,

Im trying to do a select statement. I want to return all columns, but only need the entries with the most recent effective data for each tax code. ie

Code:

TaxCode	TaxType	Rate	EffectiveDate	Cost
STD	0	17.500	1900-01-01 00:00:00.000	0.000
STD	0	15.000	2008-12-01 00:00:00.000	0.000
STD	0	15.000	2009-07-01 00:00:00.000	0.000
STD	0	15.000	2009-08-03 00:00:00.000	0.000
STD	0	17.500	2010-01-01 00:00:00.000	0.000

Should only return

Code:

STD	0	17.500	2010-01-01 00:00:00.000	0.000

I'm doing this in sql management studio 2008

Thanks in advance

A[L]C · 17 Mar 2010 at 15:19

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'GROUP'.

A[L]C · 17 Mar 2010 at 15:20

If I swap order by and group by around, I get

Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.TaxType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.Rate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.EffectiveDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.Cost' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.TimeStamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

A[L]C · 17 Mar 2010 at 15:35

That returned

Code:

TaxCode	TaxType	Rate	ED	Cost
STD	0	15.000	2009-08-03 00:00:00.000	0.000
STD	0	17.500	2010-01-01 00:00:00.000	0.000

getting closer!

A[L]C · 17 Mar 2010 at 15:57

How do I get rid of the 15 rate?

A[L]C · 17 Mar 2010 at 15:58

Ahh

SELECT TaxCode, TaxType, MAX(Rate) AS Rate, MAX(EffectiveDate) AS ED, Cost
FROM [weighman].[dbo].[wmTaxSchedule]
GROUP BY TaxCode, TaxType, Cost

A[L]C · 17 Mar 2010 at 16:15

Thanks everyone