We help IT Professionals succeed at work.

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
Comment
Watch Question

HuaMin ChenProblem resolver

Commented:
1. Put group by columns in your query please.
2. Put

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

in case the column is null.

Author

Commented:
im confused
what do you mean
PortletPaulEE 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

Open in new window

Scott PletcherSenior 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

Open in new window

Arifhusen AnsariBusiness 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

Open in new window



Hope it will help you.

Author

Commented:
Thanks guys!