Solved

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

Posted on 2012-03-21
20
314 Views
Last Modified: 2012-04-05
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
0
Comment
Question by:tommym121
  • 5
  • 4
  • 3
  • +4
20 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 29

Expert Comment

by:feridun
Comment Utility
try:

select * from yourtablename where date = (select max(date) from yourtablename )
0
 

Author Comment

by:tommym121
Comment Utility
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
 
LVL 29

Expert Comment

by:feridun
Comment Utility
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
 
LVL 29

Expert Comment

by:feridun
Comment Utility
It may be easier if there is an id column that can be used. Is there one?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
Comment Utility
How many different codes you may have in this table?
0
 
LVL 18

Accepted Solution

by:
Victoria Yudin earned 500 total points
Comment Utility
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
 

Author Comment

by:tommym121
Comment Utility
Abdulmalek_Hamsho

There is no fix number of code.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
Comment Utility
Well done Victoria. I was trying to do it using recursion, but yours is much straight forward.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
Comment Utility
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
 

Author Comment

by:tommym121
Comment Utility
Abdulmalek_Hamsho

It will grow, but I have 50  now.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Hi,

  did you actually check my article already?

  as it's sql server, that one should give you the solution easily
0
 
LVL 4

Expert Comment

by:Nguyen Doan Tien
Comment Utility
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
 
LVL 8

Expert Comment

by:Leo Torres
Comment Utility
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
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
Comment Utility
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
 
LVL 8

Expert Comment

by:Leo Torres
Comment Utility
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
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
Comment Utility
Yes, I think this one is gonna work.
0
 
LVL 8

Expert Comment

by:Leo Torres
Comment Utility
yes it should i will return first place with ties
0
 

Author Closing Comment

by:tommym121
Comment Utility
Thanks
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

763 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

12 Experts available now in Live!

Get 1:1 Help Now