Solved

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

Posted on 2012-03-21
20
328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +4
20 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37747511
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 Kadir
ID: 37747535
try:

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

Author Comment

by:tommym121
ID: 37747580
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 29

Expert Comment

by:Feridun Kadir
ID: 37747697
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 Kadir
ID: 37747740
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
ID: 37750458
How many different codes you may have in this table?
0
 
LVL 18

Accepted Solution

by:
Victoria Yudin earned 500 total points
ID: 37750604
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
ID: 37750642
Abdulmalek_Hamsho

There is no fix number of code.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 37750647
Well done Victoria. I was trying to do it using recursion, but yours is much straight forward.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 37750657
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
ID: 37753281
Abdulmalek_Hamsho

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37760467
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
ID: 37762810
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
ID: 37769160
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
ID: 37769242
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
ID: 37769258
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
ID: 37769284
Yes, I think this one is gonna work.
0
 
LVL 8

Expert Comment

by:Leo Torres
ID: 37769289
yes it should i will return first place with ties
0
 

Author Closing Comment

by:tommym121
ID: 37814999
Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

726 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