Solved

query question - where clause with a max involved

Posted on 2016-09-20
7
78 Views
Last Modified: 2016-09-23
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
0
Comment
Question by:jamesmetcalf74
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 41808006
1. Put group by columns in your query please.
2. Put

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

in case the column is null.
0
 

Author Comment

by:jamesmetcalf74
ID: 41808008
im confused
what do you mean
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 83 total points
ID: 41808020
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 52

Accepted Solution

by:
Ryan Chong earned 251 total points
ID: 41808076
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 83 total points
ID: 41808881
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
 
LVL 13

Assisted Solution

by:Arifhusen Ansari
Arifhusen Ansari earned 83 total points
ID: 41810475
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
 

Author Closing Comment

by:jamesmetcalf74
ID: 41813215
Thanks guys!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question