Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

query question - where clause with a max involved

Posted on 2016-09-20
7
Medium Priority
?
82 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 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 332 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 54

Accepted Solution

by:
Ryan Chong earned 1004 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 332 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 332 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

916 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