query question - where clause with a max involved

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
jamesmetcalf74Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ryan ChongConnect With a Mentor 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

Open in new window

0
 
HuaMinChenBusiness AnalystCommented:
1. Put group by columns in your query please.
2. Put

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

in case the column is null.
0
 
jamesmetcalf74Author Commented:
im confused
what do you mean
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
PortletPaulConnect With a Mentor freelancerCommented:
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)
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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

Open in new window

0
 
Arifhusen AnsariConnect With a Mentor Business Intelligence Developer and AnalystCommented:
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

Open in new window



Hope it will help you.
0
 
jamesmetcalf74Author Commented:
Thanks guys!
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.

All Courses

From novice to tech pro — start learning today.