CODEWITHSUNDEEP
mysqlsqlwhere-clause
WillT2118
WillT2118

Reputation: 41

How to Obtain First and Last record ? One Step Solution?

I have the following data table. 

 Record  Date        Price 
 A       3/1/2015    5 
 A       3/2/2015    6 
 A       3/3/2015    7 
 A       3/4/2015    10 
 B       2/1/2015    4 
 B       2/2/2015    6 
 B       2/3/2015    15 
 B       2/4/2015    2

How can I output a table that only shows the First price and the last price for each record for the first date in the table and the last date in the table. Output columns would be Record, First Price, Last Price. I am looking for a one step solution that is easy to implement in order to create a custom view.

The output desired would be:

 Record     FirstPrice   LastPrice
 A          5            10 
 B          4            2

Upvotes: 0

Views: 79

Answers (3)

Erik Blessman
Erik Blessman

Reputation: 693

Get the min and max aggregate dates grouped by the record field and join back to the root data. If you can have multiple records for the same record field on the same date, you will have to use min, max or avg to get just one value for that date.

SQLFiddle: http://sqlfiddle.com/#!9/1158b/3

SELECT anchorData.Record
,      firstRecord.Price
,      lastRecord.Price
FROM (
    SELECT Record
    ,      MIN(Date) AS FirstDate
    ,      MAX(Date) AS LastDate
    FROM   Table1
    GROUP BY Record
    ) AS anchorData
JOIN   Table1 AS firstRecord
ON     firstRecord.Record = anchorData.Record
AND    firstRecord.Date = anchorData.FirstDate
JOIN   Table1 AS lastRecord
ON     lastRecord.Record = anchorData.Record
AND    lastRecord.Date = anchorData.LastDate

Upvotes: 0

mituw16
mituw16

Reputation: 5250

Perhaps something like this is what you are looking for?

select R.Record, FD.Price as MinPrice, LD.Price as MaxPrice
from Records R
join (
    select Price, R1.Record
    from Records R1
    where Date = (select MIN(DATE) from Records R2 where R2.Record = R1.Record)
  ) FD on FD.Record = R.Record
join (
   select Price, R1.Record
    from Records R1
    where Date = (select MAX(DATE) from Records R2 where R2.Record =     R1.Record)
  ) LD on LD.Record = R.Record
group by R.Record

http://sqlfiddle.com/#!9/d047b/26

Upvotes: 4

Pigasus
Pigasus

Reputation: 130

"in order to create a custom view."...are you looking to do this in Oracle/MySql as a CREATE VIEW or just a query/select statement?

Upvotes: -1

Related Questions