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.

please refer to spreadsheet.
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:
1. Put group by columns in your query please.
2. Put

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

in case the column is null.

Commented:
im confused
what do you mean
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
select
c.Accountnumber, c.name, a.Address, g.reading, g.[date]
from customer c
inner join address a on c.Accountnumber = a.Accountnumber
left join (
select Accountnumber, reading, [date]
, 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)
Software Team Lead
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,
b.Reading Reading1, b.Date Date1,
c.Reading Reading2, c.Date Date2,
d.Address
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,
g.most_recent_reading, g.most_recent_date,
g.[2nd_most_recent_reading], g.[2nd_most_recent_date],
a.Address
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 Reading END) AS most_recent_reading,
MAX(CASE WHEN row_num = 1 THEN Date END) AS most_recent_date,
MAX(CASE WHEN row_num = 2 THEN Reading END) AS [2nd_most_recent_reading],
MAX(CASE WHEN row_num = 2 THEN Date END) AS [2nd_most_recent_date]
FROM (
SELECT Accountnumber, Reading, Date,
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
``````
Business Intelligence Developer and Analyst
Top Expert 2015
Commented:
Please refer the below scrip.

it might help you.

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
,Reading 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')

CREATE TABLE #Address

(
Accountnumber INT,
Address VARCHAR(100)
)

INSERT INTO #Address
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
, Latest.Reading [most recent reading]
,Latest.Date [read date]
,SecondLatest.Reading [2nd most recent reading]
,SecondLatest.Date [2nd read date]
,#Address.Address
FROM #Customer
INNER JOIN #Address
ON #Address.Accountnumber= #Customer.Accountnumber
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

DROP TABLE #Address
DROP TABLE #Customer
DROP TABLE #Gallons
``````

Hope it will help you.

Commented:
Thanks guys!