Solved

query question - where clause with a max involved

Posted on 2016-09-20
7
64 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
7 Comments
 
LVL 10

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 48

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 50

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 12

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

776 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