help selecting the max(date) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help selecting the max(date)

Author

Topic

fudgey200
Starting Member

2 Posts

Posted - 2012-08-06 : 09:47:21

Hello all. Im new here in need of some help.

I have tables,

Orders
product_version
completion_status
completion_date
phonenumber
sample:
24521 cu 2010-01-05 5553235486
24621 cu 2012-02-10 5553235486
Product
product_version
product_type
sample:
24521 DC
24621 NI

I'm trying to get these results, the max(completion_date) per phone_number:
PhoneNumber Completion_date product_version product_type
5553235486 2012-02-10 24621 NI

Help Please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 10:07:32

[code]
SELECT o.PhoneNumber,o.Completion_date,p.product_version,p.product_Type
FROM Orders o
INNER JOIN (SELECT phonenumber,MAX(completion_date) AS latest
FROM Orders
GROUP BY phonenumber
)o1
ON o1.phonenumber = o.phonenumber
AND o1.latest = o.completion_date
INNER JOIN Product p
ON p.product_version = o.product_version
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fudgey200
Starting Member

2 Posts

Posted - 2012-08-06 : 10:33:33

Thanks so much for your response but i still have a problem.
My PhoneNumber field is actually a concatnate.
npa || nxx || lineno || as PhoneNumber

I'm getting the error "PHONENUMBER" invalid identifier

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 10:44:15

then replace phonenumber with actual concatenation expression


SELECT o.PhoneNumber,o.Completion_date,p.product_version,p.product_Type
FROM (SELECT *,npa || nxx || lineno || as PhoneNumber FROM Orders) o
INNER JOIN (SELECT npa || nxx || lineno || as PhoneNumber,MAX(completion_date) AS latest
            FROM Orders
            GROUP BY npa || nxx || lineno || 
            )o1
ON o1.phonenumber = o.phonenumber
AND o1.latest = o.completion_date
INNER JOIN Product p
ON p.product_version = o.product_version

Also this is MS SQL Server forum and i think you're using Oracle

I can guarantee above solution working in sql server but not in Oracle

you may make a try and if not working try this in some oracle forums

there are not many Oracle experts here and I personally is not an expert

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources