• Status: Solved
• Priority: Medium
• Security: Public
• Views: 4698

# SQL Select Max Date and Previous Date?

I'm having trouble selecting the Max date and Previous date. I tried the following code and it just changes the date, the price column data remains the same.

[Max Date w/ Price]
select Price, Max(date)
from table1
where Code = 4235
group by Price

--- Result ---
Price              EffDate
1234.0000      2012-07-28 00:00:00.000
1255.0000      2012-07-30 00:00:00.000

[Previous Date w/ Price]
select Price, Max(date) -1
from table1
where Code = 4235
group by Price

--- Result ---
Price              EffDate
1234.0000      2012-07-27 00:00:00.000
1255.0000      2012-07-29 00:00:00.000

[Previous Date w/ Price]
select Price, Max(date) -1
from table1
where Code = 4235
group by Price

Then I tried moving the Max Date into the Where Clause like so, but it needs a Having clause:

[Previous Date w/ Price]
select Price, Date
from table1
where Code = 4235
and Date = Max(Date) -1
group by Price

Help...
0
WorknHardr
2 Solutions

Commented:
you can't put agregate functions (like max, min, sum, count, avg) on a where clause. A where clause filters original rows from the table.

What you need is to use a having clause to filter groups (having is a kind of where but after make the group by), or you can use a subquery. Try something like this:

select Price, Date
from table1
where Code = 4235
and Date = (SELECT Max(Date) -1 FROM table1)
group by Price
0

you need to write it as below

``````select Price, Date
from table1
where Code = 4235
group by Price
having Date = Max(Date) -1
``````
0

Commented:
I think MAX(Date) -1 is incorrect.

That will be the day before the last date, potentially no data.

What is needed is a Top 2 selection.
To derive the 2nd newest date...

``````SELECT MIN(a.Date) AS SecondNewestDate
FROM
(SELECT TOP 2 Date
FROM Table
WHERE Code = 4235
ORDER BY Date DESC) a
``````

T
0

Author Commented:
No matter which solution I use, doesn't work when I join tables.
0

check this out

``````DECLARE @T TABLE
(
Price INT, Date Datetime,code INT
)

insert into @T VALUES (123,GETDATE(),4235)
insert into @T VALUES (124,GETDATE(),4235)
insert into @T VALUES (228,GETDATE()-1,4235)
insert into @T VALUES (223,GETDATE()-1,4235)
insert into @T VALUES (128,GETDATE()-2,4235)
insert into @T VALUES (123,GETDATE()-2,4235)

-- all the above code is used to setup temporary or dummy data...

SELECT T1.* FROM @T T1
,(
select Max(Date)    date
from @t
where Code = 4235
) T2
WHERE T1.code = 4235 and t1.date = t2.date -1
``````
0

Author Commented:
I changed the Price and Date values to a more realistic test. I just need to Join more tables to see if it still works as well.

DECLARE @T TABLE
(
Price INT, Date Datetime,code INT
)

insert into @T VALUES (300,GETDATE()-1,4235)
insert into @T VALUES (200,GETDATE()-2,4235)
insert into @T VALUES (100,GETDATE()-3,4235)
insert into @T VALUES ( 50,GETDATE()-4,4235)

-- all the above code is used to setup temporary or dummy data...

[Max Date]
SELECT Top 1 T1.* FROM @T T1
,(
select Max(Date)    date
from @t
where Code = 4235
) T2
WHERE T1.code = 4235 and t1.date = t2.date

[Max Date -1]
SELECT Top 1 T1.* FROM @T T1
,(
select Max(Date)    date
from @t
where Code = 4235
) T2
WHERE T1.code = 4235 and t1.date = t2.date -1
0

Commented:
select price, date from
(select price, date, row_number() over (partition by code order by date desc) rn
from table1
where code = 4235) as x
where x.rn <= 2
0

Author Commented:
thank you all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.