We help IT Professionals succeed at work.

# query question - where clause with a max involved

on
I hope you can deduce what i am asking from the excel spreadsheet
it has 3 tables listed and the desired results.  I don't know how to write this query.

the problem area is the gallons table.

it has to do with selecting row data from a max value where the max value is not known.  and then a 2nd max also
experts-exchange.xlsx
Comment
Watch Question

## View Solutions Only

Problem resolver

Commented:
2. Put

max(isnull(column1,0))...

in case the column is null.

Commented:
im confused
what do you mean
Most Valuable Expert 2014
Awarded 2013
Commented:
select
from customer c
inner join address a on c.Accountnumber = a.Accountnumber
left join (
, row_number() over(partition by Accountnumber order by [date] DESC) as rn
from gallons
) g on c.Accountnumber = g.Accountnumber and g.rn = 1

Using ROW_NUMBER() OVER()  is very useful for locating whole rows for "latest" or "maximum" (see the bold bits above)
Commented:
you can try something like this too:
``````with cte as
(
select a.*, row_number() over (partition by a.Accountnumber order by a.Date desc) idx
from Gallons a
)
Select
a.Accountnumber, a.Name,
from Customer a
left join (select * from cte where idx = 1) b on a.Accountnumber = b.Accountnumber
left join (select * from cte where idx = 2) c on a.Accountnumber = c.Accountnumber
left join Address d on a.Accountnumber = d.Accountnumber
Where a.Accountnumber = 1111
``````
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
``````SELECT
c.Accountnumber, c.name,
FROM dbo.Customer c
LEFT OUTER JOIN dbo.Address a ON c.Accountnumber = a.Accountnumber
LEFT OUTER JOIN (
SELECT Accountnumber,
MAX(CASE WHEN row_num = 1 THEN Date END) AS most_recent_date,
MAX(CASE WHEN row_num = 2 THEN Date END) AS [2nd_most_recent_date]
FROM (
ROW_NUMBER() OVER(PARTITION BY Accountnumber ORDER BY [date] DESC) AS row_num
FROM dbo.Gallons
) AS derived
WHERE row_num IN (1, 2)
GROUP BY Accountnumber
) AS g ON g.Accountnumber = c.Accountnumber
``````
Top Expert 2015
Commented:

I have created the sample table and data as you have explained.

``````CREATE TABLE #Customer
(Accountnumber	INT ,
name VARCHAR(100))
INSERT INTO #Customer
VALUES
(1111,'john')
,(2222,'jim'	)

CREATE TABLE #Gallons
(

Accountnumber INT
,[Date] DATE

)

INSERT INTO #Gallons
VALUES
(1111,20,'1/1/2016')
,(1111,40,'1/2/2016')
,(1111,60,'1/3/2016')
,(2222,20,'1/4/2016')
,(2222,40,'1/5/2016')
,(2222,60,'1/6/2016')

(
Accountnumber INT,
)

VALues
(1111,'100 main')
,(2222,'200 main')

;WITH Data_Manipulation
AS
(

SELECT *
,ROW_NUMBER() OVER(PARTITION BY Accountnumber ORDER BY [Date] DESC) LatestSeq
FROM #Gallons

)

SELECT #Customer.Accountnumber
, #Customer.name
FROM #Customer
LEFT OUTER JOIN Data_Manipulation as Latest
ON #Customer.Accountnumber=Latest.Accountnumber
AND Latest.LatestSeq=1

LEFT OUTER JOIN Data_Manipulation SecondLatest
ON #Customer.Accountnumber=SecondLatest.Accountnumber
AND SecondLatest.LatestSeq=2