Solved

# SQL Select Max Date and Previous Date?

Posted on 2014-01-29
Medium Priority
3,485 Views
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
Question by:WorknHardr

LVL 15

Expert Comment

ID: 39818449
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

LVL 16

Expert Comment

ID: 39818669
you need to write it as below

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

LVL 12

Expert Comment

ID: 39818759
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 Comment

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

LVL 16

Accepted Solution

Surendra Nath earned 1000 total points
ID: 39821113
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 Comment

ID: 39821378
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

LVL 32

Assisted Solution

awking00 earned 1000 total points
ID: 39822035
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 Closing Comment

ID: 39833701
thank you all
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works