locked
A view that finds maximum values from a table RRS feed

  • Question

  • I have two tables for land parcels, the first table Parcel_Table contains the land parcels with their areas (each parcel is represented once with no duplication), the second table Parcel_Estimation contains the details about each parcel, in this table each parcel have many records for each estimation year (e.g. a parcel may have three estimation years: 2009, 2012, 2015 and so it has three records in the table) as shown in the screenshot below.

    I want to create a view that links the two table based on the Parcel_ID column and contains unique row for each parcel; in this view there are two new columns [Last_Estimation_Year] and [Last_Price], the [Last_Estimation_Year] compares all the estimation years for each parcel in the Parcel_Estimation table and finds the latest estimation year and populates the [Last_Estimation_Year] column in the view by the last (maximum) year, and according to this year, the [Last_Price] is computed.

    Is there any way to do this?

    Any help is appreciated,


    Hani Draidi GIS Engineer

    Wednesday, January 27, 2016 11:52 AM

Answers

  • Yes

    you can simply do this

    SELECT p.*,
    e.Estimation_Year AS Last_Estimation_Year,
    e.Estimated_Price AS Last_Estimated_Price
    FROM Parcel_Table p
    INNER JOIN (
    SELECT ROW_NUMBER() OVER (PARTITION BY Parcel_ID ORDER BY Estimation_Year DESC) AS Seq,*
    FROM Parcel_Estimation
    )e
    On e.Parcel_ID = p.ID
    AND e.Seq = 1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, January 27, 2016 12:01 PM
  • Try the below:

    create Table Parcel_Table (Id int identity(1,1), Parcel_Code int, Area int) Insert into Parcel_Table (Parcel_Code,Area) Values(11,1000),(12,2000) create Table Parcel_Estimation (Id int identity(1,1), Parcel_id int, Estimation_Year int, Estimated_Price int) Insert into Parcel_Estimation (Parcel_id,Estimation_Year,Estimated_Price) Values(11,2009,1000),(11,2012,2000),(11,2015,3000),(12,2005,5000),(12,2010,65000)

    --View Creation create view test_View as Select a.id,a.Parcel_code,A.Area,MAX(B.Estimation_Year)Last_Execution_Year,Max(B.Estimated_Price) Last_Price From Parcel_Table a Inner join Parcel_Estimation B on a.Parcel_Code = B.Parcel_id Group by a.id,a.Parcel_code,A.Area Select * From test_View



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]

    Wednesday, January 27, 2016 12:03 PM
    Answerer
  • Modify this piece of code 

    SELECT ROW_NUMBER() OVER (PARTITION BY Parcel_ID ORDER BY 
    Estimated_Price
    DESC) AS Seq,*


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 27, 2016 12:26 PM
  • Hi,

    for something like this its best to use apply.

    CREATE VIEW dbo.Parcel_View
    AS
    SELECT p.ID, p.Parcel_Code, p.Area, e.Estimation_Year AS Last_Estimation_Year, e.Estimation_Price AS Last_Price
    FROM dbo.Parcel_Table AS p
    OUTER APPLY (
    	SELECT TOP 1 *
    	FROM dbo.Parcel_Estimation AS e
    	WHERE p.Parcel_Code = e.Parcel_ID
    	ORDER BY e.Estimation_Year DESC) AS e
    Wednesday, January 27, 2016 12:27 PM
  • Many Thanks Latheesh NK for the valuable help,

    I tried your suggestion and it worked, but their is a note, I want to populate the Last_Price column with the price at the maximum year (the maximum year does not guarantee the maximum price as in Parcel_ID= 14 in the screenshot above)

    Once again, thank you!


    Hani Draidi GIS Engineer

    This is contradictory to what you specified in the first post ie

    populates the [Last_Estimation_Year] column in the view by the last (maximum) year, and according to this year, the [Last_Price] is computed.

    Which is what I gave you the solution for

    As per this latest post what you need is code like above

    SELECT p.*,
    e.Estimation_Year AS Last_Estimation_Year,
    e.Estimated_Price AS Last_Estimated_Price
    FROM Parcel_Table p
    INNER JOIN (
    SELECT ROW_NUMBER() OVER (PARTITION BY Parcel_ID ORDER BY Estimation_Price DESC) AS Seq,*
    FROM Parcel_Estimation
    )e
    On e.Parcel_ID = p.ID
    AND e.Seq = 1

    And to make it view

    CREATE VIEW LatestParcelPrice
    AS
    SELECT p.*,
    e.Estimation_Year AS Last_Estimation_Year,
    e.Estimated_Price AS Last_Estimated_Price
    FROM Parcel_Table p
    INNER JOIN (
    SELECT ROW_NUMBER() OVER (PARTITION BY Parcel_ID ORDER BY Estimation_Year DESC) AS Seq,*
    FROM Parcel_Estimation
    )e
    On e.Parcel_ID = p.ID
    AND e.Seq = 1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, January 27, 2016 12:36 PM

All replies

  • Yes

    you can simply do this

    SELECT p.*,
    e.Estimation_Year AS Last_Estimation_Year,
    e.Estimated_Price AS Last_Estimated_Price
    FROM Parcel_Table p
    INNER JOIN (
    SELECT ROW_NUMBER() OVER (PARTITION BY Parcel_ID ORDER BY Estimation_Year DESC) AS Seq,*
    FROM Parcel_Estimation
    )e
    On e.Parcel_ID = p.ID
    AND e.Seq = 1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, January 27, 2016 12:01 PM
  • Try the below:

    create Table Parcel_Table (Id int identity(1,1), Parcel_Code int, Area int) Insert into Parcel_Table (Parcel_Code,Area) Values(11,1000),(12,2000) create Table Parcel_Estimation (Id int identity(1,1), Parcel_id int, Estimation_Year int, Estimated_Price int) Insert into Parcel_Estimation (Parcel_id,Estimation_Year,Estimated_Price) Values(11,2009,1000),(11,2012,2000),(11,2015,3000),(12,2005,5000),(12,2010,65000)

    --View Creation create view test_View as Select a.id,a.Parcel_code,A.Area,MAX(B.Estimation_Year)Last_Execution_Year,Max(B.Estimated_Price) Last_Price From Parcel_Table a Inner join Parcel_Estimation B on a.Parcel_Code = B.Parcel_id Group by a.id,a.Parcel_code,A.Area Select * From test_View



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]

    Wednesday, January 27, 2016 12:03 PM
    Answerer
  • Many Thanks Latheesh NK for the valuable help,

    I tried your suggestion and it worked, but their is a note, I want to populate the Last_Price column with the price at the maximum year (the maximum year does not guarantee the maximum price as in Parcel_ID= 14 in the screenshot above)

    Once again, thank you!


    Hani Draidi GIS Engineer

    Wednesday, January 27, 2016 12:23 PM
  • Modify this piece of code 

    SELECT ROW_NUMBER() OVER (PARTITION BY Parcel_ID ORDER BY 
    Estimated_Price
    DESC) AS Seq,*


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 27, 2016 12:26 PM
  • Many Thanks Visakh for the prompt input,


    Hani Draidi GIS Engineer

    Wednesday, January 27, 2016 12:26 PM
  • Hi,

    for something like this its best to use apply.

    CREATE VIEW dbo.Parcel_View
    AS
    SELECT p.ID, p.Parcel_Code, p.Area, e.Estimation_Year AS Last_Estimation_Year, e.Estimation_Price AS Last_Price
    FROM dbo.Parcel_Table AS p
    OUTER APPLY (
    	SELECT TOP 1 *
    	FROM dbo.Parcel_Estimation AS e
    	WHERE p.Parcel_Code = e.Parcel_ID
    	ORDER BY e.Estimation_Year DESC) AS e
    Wednesday, January 27, 2016 12:27 PM
  • Many Thanks Latheesh NK for the valuable help,

    I tried your suggestion and it worked, but their is a note, I want to populate the Last_Price column with the price at the maximum year (the maximum year does not guarantee the maximum price as in Parcel_ID= 14 in the screenshot above)

    Once again, thank you!


    Hani Draidi GIS Engineer

    This is contradictory to what you specified in the first post ie

    populates the [Last_Estimation_Year] column in the view by the last (maximum) year, and according to this year, the [Last_Price] is computed.

    Which is what I gave you the solution for

    As per this latest post what you need is code like above

    SELECT p.*,
    e.Estimation_Year AS Last_Estimation_Year,
    e.Estimated_Price AS Last_Estimated_Price
    FROM Parcel_Table p
    INNER JOIN (
    SELECT ROW_NUMBER() OVER (PARTITION BY Parcel_ID ORDER BY Estimation_Price DESC) AS Seq,*
    FROM Parcel_Estimation
    )e
    On e.Parcel_ID = p.ID
    AND e.Seq = 1

    And to make it view

    CREATE VIEW LatestParcelPrice
    AS
    SELECT p.*,
    e.Estimation_Year AS Last_Estimation_Year,
    e.Estimated_Price AS Last_Estimated_Price
    FROM Parcel_Table p
    INNER JOIN (
    SELECT ROW_NUMBER() OVER (PARTITION BY Parcel_ID ORDER BY Estimation_Year DESC) AS Seq,*
    FROM Parcel_Estimation
    )e
    On e.Parcel_ID = p.ID
    AND e.Seq = 1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, January 27, 2016 12:36 PM
  • Thanks all,

    I tried your suggestions, but I am getting blank results as shown below: 



    Hani Draidi GIS Engineer


    • Edited by Hani Draidi Tuesday, February 2, 2016 11:47 AM
    Tuesday, February 2, 2016 9:51 AM