Click here to Skip to main content
15,937,012 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Currency TimeStamp Rate
LPB 2019-01-01 1.0000
LPB 2019-01-06 1.0000
USD 2019-01-01 1500.0000
USD 2019-01-06 1057.5000

Result must show only
LPB 2019-01-06 1.0000
USD 2019-01-06 1057.5000

What I have tried:

SELECT Distinct([Currency]),MAX([TimeStamp]),[Rate] FROM [DB_SchoolManager].[dbo].[tbl_Currencies] 
group by [Currency],[Rate]
Posted
Updated 8-Jan-19 0:20am

This sounds like homework so won't be giving the dull answer. However, to point you to right direction, you can use ORDER BY Clause (Transact-SQL) - SQL Server | Microsoft Docs[^] and TOP (Transact-SQL) - SQL Server | Microsoft Docs[^] for the task.
 
Share this answer
 
Comments
Maciej Los 6-Jan-19 11:52am    
;)
In addition to Wendelius[^] answer:
SQL
USE [DB_SchoolManager];

--Using MAX + GROUP BY
SELECT T.*
FROM tbl_Currencies AS T INNER JOIN (
	SELECT Currency, MAX([TimeStamp]) AS [TimeStamp]
	FROM tbl_Currencies 
	GROUP BY Currency 
) AS H ON T.Currency = H.Currency AND T.[TimeStamp] = H.[TimeStamp] 

--Using ROW_NUMBER() inline function
SELECT T.*
FROM (
	SELECT *, ROW_NUMBER() OVER(PARTITION BY Currency ORDER BY [TimeStamp] DESC) AS Rn
	FROM tbl_Currencies
	) AS T
WHERE T.Rn=1


Both queries return:
USD	2019-01-06	1057.5
LPB	2019-01-06	1


For further details, please see:
Visual Representation of SQL Joins[^]
ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs[^]
GROUP BY (Transact-SQL) - SQL Server | Microsoft Docs[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900