Select Most Recent Entry in SQL -


I am trying to select the most recent non zero entry from my data set in SQL. Most examples of this are only satisfied with returning dates and groups from variables, but I would also like to return the relevant values. For example: 

  ID date value ---------------------------- 001 2014-10- 01 32 001 2014-10-05 10 001 2014-10-17 002 2014-10-03 17 002 2014-10-20 60 003 2014-09-30 90 003 2014-10-10 7 004 2014-10-06 150 005 2014-10-17 0 005 2014-10-189 Select the ID from the MDAT as MAX (date), where the value and gt; 0

Returns by Group ID: 

  ID date ------------------- 001 2014 -10-05 002 2014-10-20 003 2014-10-10 004 2014-10-06 005 2014-10-18

But whenever I include the value as a The selected variable, the SQLServer result occurs in an error:

"The column value is invalid in the selection list because it is not contained in a consolidated work or group by section . "

My desired result will be: 

  ID date value --------------------- - ----- 001 2014-10-05 10 002 2014-10-20 60 003 2014-10-10 7 004 2014-10-06 150 005 2014-10-18 9

I thought that a solution would be to return results in the original table and return the value corresponding to the relevant ID; The date (I have already sorted down and so I know it's unique), but it seems like a dirty solution to me. Any help on this will be appreciated.

Note: I do not want to group the price, because this is the result, I'm finally trying to get out (i.e. for each ID, I want the most recent value). Forward Example: 

  ID Date Values ​​---------------------------- 001 2014-10-05 10 001 2014-10-06 10 001 2014-10-10 10 001 2014-10-12 8 001 2014-10-18 0

Here, I want only the last non zero entry . (001, 2014-10-12, 8) 

  Select ID, Max (date) as MDTE, table with value where price & gt; ID will return to group, value

: 

  ID date value ---------------- - ----------- 00-10-2010-10 10 001 2014-10-12 8

Assume that you do not have duplicated dates for the same id in the table, it should work: 

  SELECT A .ID, A.Det, A. Valuation F1 T1 as an Inner Join (Selection ID, Max (Date) A1 Date in Date 19 and ID; ID by Group 0) Date as BAID = BID and Aadet = / Code>

Comments

Post a Comment