Solved

SQL Select Statement

Posted on 2008-11-03
3
199 Views
Last Modified: 2010-03-20
Hi,

I have a table which contains prices for assets. The asset is not priced everday. All I need to do is get the latest price for all assets held in the table..?

Table example:

Asset                         Price Date  (UK Dates)                Price
A                                03/11/2008                                  4
A                                01/01/2008                                  5
A                                01/07/2007                                  6
B                                01/02/2008                                  7  
B                                01/01/2005                                  7
B                                01/07/2004                                  8  
C                                01/07/2004                                  18  

The SQL should return...
A                                03/11/2008                                  4
B                                01/02/2008                                  7  
C                                01/07/2004                                  18  

Thanks in advance...
0
Comment
Question by:ISC
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22866042
you mean:
SELECT t.*
  FROM yourtable t
 WHERE t.Price_Date = (SELECT MAX(i.price_date) FROM yourtable i WHERE i.Asset = t.Asset )

Open in new window

0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22866044
select distinct asset,price,date from table t1order by price desc.
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22866052
sorry mine is wrong.ignore that one.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now