SQL - How to write a statement to gGet the latest row

I have the d
Code	Date	                                        Value
5000	2008-12-01 00:00:00.000	12000.00
5000	2010-04-01 00:00:00.000	15000.04
5000	2011-04-01 00:00:00.000	18000.42
6000	2008-12-01 00:00:00.000	12000.00
6000	2010-04-01 00:00:00.000	15000.04
6000	2011-04-01 00:00:00.000	18000.42
tommym121Asked:
Who is Participating?
 
Victoria YudinConnect With a Mentor Owner / Dynamics GP ConsultantCommented:
I believe you should be able to do something like the following code, however, if you have multiple records with the same Code and Date but with different Values, you will get multiple results for the same Code/Date combination.  

SELECT DISTINCT MD.Last_Date, a.Code, a.Value
FROM  Your_Table a  
INNER JOIN
(SELECT max([Date]) Last_Date, Code
 FROM Your_Table
 GROUP BY Code) MD -- max date
    ON a.Code = MD.Code	AND a.[Date] = MD.Last_Date

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I am not sure if my article applies "as is" for MS Dynamics, but please check it anyhow:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
Feridun KadirPrincipal ConsultantCommented:
try:

select * from yourtablename where date = (select max(date) from yourtablename )
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
tommym121Author Commented:
Sorry I was not able to complete the question,

I have the data below.  I am try to extract the most current data (latest Data) fore each code
Code      Date                                              Value
5000      2008-12-01 00:00:00.000      12000.00
5000      2010-04-01 00:00:00.000      15000.04
5000      2011-04-01 00:00:00.000      18000.42
6000      2008-12-01 00:00:00.000      22000.00
6000      2010-04-01 00:00:00.000      25000.04
6000      2011-04-01 00:00:00.000      28000.42

This is the result I try to achieve

5000      2011-04-01 00:00:00.000      18000.42
6000      2011-04-01 00:00:00.000      28000.42
0
 
Feridun KadirPrincipal ConsultantCommented:
Hmm, that is proving tricky. I can get you the code and date with:

select code,  MAX(date) from tbalename group by code

but not the value.

If I come with an answer I'll post again.
0
 
Feridun KadirPrincipal ConsultantCommented:
It may be easier if there is an id column that can be used. Is there one?
0
 
Abdulmalek_HamshoCommented:
How many different codes you may have in this table?
0
 
tommym121Author Commented:
Abdulmalek_Hamsho

There is no fix number of code.
0
 
Abdulmalek_HamshoCommented:
Well done Victoria. I was trying to do it using recursion, but yours is much straight forward.
0
 
Abdulmalek_HamshoCommented:
I think you have the answer from Victoria. But for clarification: My question was: What's the max number of Distinct codes you may have in this table? 100, 200, or 100000?
0
 
tommym121Author Commented:
Abdulmalek_Hamsho

It will grow, but I have 50  now.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Hi,

  did you actually check my article already?

  as it's sql server, that one should give you the solution easily
0
 
Nguyen Doan TienProject ManagerCommented:
Hi tommym121,
Here is the simplest solution:
SELECT * FROM test
WHERE date IN (SELECT MAX(date) FROM test);

Open in new window


Snap shoot:tested solution
0
 
Leo TorresSQL DeveloperCommented:
Here  is another way
What I like about this way is that you can change rn to be any place if you wanted  all
second or third place you would be just change 1 to place you can even return top 2 or 3 easily from here

 Select a.code, a.Date, a.Value
 From (
 Select Code, Date,Value,
 ROW_NUMBER() Over (PARTITION by code Order by Date Desc) rn
 from YOUR_TABLE_HERE ) a
 Where a.rn = 1 

Open in new window

0
 
Abdulmalek_HamshoCommented:
ltorres321, we need to show all of the row with the most recent date for every code, while your script shows one row only. Example:

5000      2008-12-01 00:00:00.000      12000.00
5000      2010-04-01 00:00:00.000      15000.04
5000      2011-04-01 00:00:00.000      18000.42
7000      2008-12-01 00:00:00.000      22000.00
7000      2011-04-01 00:00:00.000      25000.04
7000      2011-04-01 00:00:00.000      28000.42

In this example, your code may show:
5000      2011-04-01 00:00:00.000      18000.42
7000      2011-04-01 00:00:00.000      25000.04

or:
5000      2011-04-01 00:00:00.000      18000.42
7000      2011-04-01 00:00:00.000      28000.42

But, the user need to show " (latest Data) fore each code":
5000      2011-04-01 00:00:00.000      18000.42
7000      2011-04-01 00:00:00.000      25000.04
7000      2011-04-01 00:00:00.000      28000.42
0
 
Leo TorresSQL DeveloperCommented:
try
Select a.code, a.Date, a.Value
 From (
 Select Code, Date,Value,
 DENSE_RANK() Over (PARTITION by code Order by Date Desc) rn
 from YOUR_TABLE_HERE ) a
 Where a.rn = 1 

Open in new window

0
 
Abdulmalek_HamshoCommented:
Yes, I think this one is gonna work.
0
 
Leo TorresSQL DeveloperCommented:
yes it should i will return first place with ties
0
 
tommym121Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.