Select Distinct Security 1

[straatlang]

Who can help me with this problem.

I want to create query from the Table Marketprices ?

SECURITY PRICEDAT PRICE
ABC 12/12/02 10
ABC 13/12/02 11
ABC 14/12/02 10
DEF 15/12/02 20
GHJ 13/12/02 21
GHJ 16/12/02 25

The result should the last know price and pricedate on every security but I only want to see each security ones and only the last pricedate.

Expected result

SECURITY DATE PRICE
ABC 14/12/02 10
DEF 15/12/02 20
GHJ 16/12/02 25

Can I use something like

SELECT DISTINCT SECURITY, DATE,PRICE
FROM MARKETPRICES

Can anybody help me.

 

[swampBoogie]

Distinct is always applied to all columns in the result so your approach will not yield the wanted result.

select * from marketPrices mp
where pricedat =
(select max(pricedat) from marketPrices
where security = mp.security)

That would return the price for the latest date for each distinct security value.

 

[straatlang]

I have two table One is the Marketprice table and the other one is
Secnames.

Marketprices

SECURITY PRICEDAT PRICE
ABC 12/12/02 10
ABC 13/12/02 11
ABC 14/12/02 10
DEF 15/12/02 20
GHJ 13/12/02 21
GHJ 16/12/02 25

Secnames

SECURITY NAME
ABC ABC Company
DEF DEF Enterprices
GHJ GHJ Atlantic

The result should the last know price and pricedate on every security but I only want to see each security ones and only the last pricedate.
and the name of the company in one report

Expected result

SECURITY DATE PRICE NAME
ABC 14/12/02 10 ABC Company
DEF 15/12/02 20 DEF Enterprices
GHJ 16/12/02 25 GHJ Atlantic

The first part from the query (This is thanks to swampBoogie's)


select * from marketPrices mp
where pricedate =
(select max(pricedate) from marketPrices
where security = mp.security)

I hope somebody maybe even SwampBoogie can help me out again.

 

[swampBoogie]

select mp.security, mp.pricedate, mp.price, s.name
from marketPrices mp inner join secnames s
on mp.security = s.security
where pricedate =
(select max(pricedate) from marketPrices
where security = mp.security)

 

[straatlang]

Hallo SwampBoogie,

This is the first time I have posted a question and the query you suggested works I just had to Copy and Past it. I did not no how else to thank so this is why I replied in this way.

Many thanks since I am not a SQL expert at all (not even a beginner ) I don't think I can help you but thanks anyway.

Regards

Straatlang