[SOLUCIONADO] Como seleccionar el campo con la fecha mas reciente de este select?

[adryan310]

Hola amigos del foro,

Tengo el siguiente select:

Código SQL:

SELECT MAX(H.[SettledDate]) AS 'LastDate', J.[Player], CONVERT(VARCHAR(8), G.[GradedDate] , 108) AS 'Time', D.[RESULT], H.[RiskAmount], H.[WinAmount], G.[VisitorTeam] + ' @ ' +G.[HomeTeam] AS 'Teams', 
       P.[PeriodDescription], CONVERT(VARCHAR(MAX),G.[VisitorNumber]) + '/' + CONVERT(VARCHAR(MAX),G.[HomeNumber]) AS 'NumGame', D.[IdWager], W.[Description]   
FROM GRADEDWAGERHEADER H, GRADEDWAGERDETAIL D, GRADEDGAME G, PERIOD P, AGENT A, WAGERTYPE W, PLAYER J
WHERE H.[IdWager]  = D.[IdWager] AND 
      D.[IdGame]   = G.[IdGame]  AND 
      H.[IdAgent]  = A.[IdAgent] AND
      H.[IdPlayer] = J.[IdPlayer] AND
      H.[IdWagerType] = W.[IdWagerType] AND
      D.[IdSport] = P.[IdSport] AND
      D.[Period]  = P.[NumberOfPeriod] AND 
      H.[IdAgent] = 5585 AND 
      D.[IdWager] = 12683461 AND
      D.[RESULT]  = 1
GROUP BY J.[Player], G.[GradedDate], D.[RESULT], H.[RiskAmount], H.[WinAmount], G.[VisitorTeam], G.[HomeTeam], 
         P.[PeriodDescription], G.[VisitorNumber], G.[HomeNumber], D.[IdWager], W.[Description]
ORDER BY TIME DESC
 

Me funciona bien, pero en algunos caso me retorna mas de un dato y lo que deseo es que me retorne solo el que tenga el campo H.[SettledDate] mas reciente, alguna ayuda al respecto?

[Libras]

Código SQL:

SELECT * FROM(
SELECT ROW_NUMBER()OVER (partition BY lastdate ORDER BY lastdate ASC) AS rn, * FROM
(
SELECT MAX(H.[SettledDate]) AS 'LastDate', J.[Player], CONVERT(VARCHAR(8), G.[GradedDate] , 108) AS 'Time', D.[RESULT], H.[RiskAmount], H.[WinAmount], G.[VisitorTeam] + ' @ ' +G.[HomeTeam] AS 'Teams', 
       P.[PeriodDescription], CONVERT(VARCHAR(MAX),G.[VisitorNumber]) + '/' + CONVERT(VARCHAR(MAX),G.[HomeNumber]) AS 'NumGame', D.[IdWager], W.[Description]   
FROM GRADEDWAGERHEADER H, GRADEDWAGERDETAIL D, GRADEDGAME G, PERIOD P, AGENT A, WAGERTYPE W, PLAYER J
WHERE H.[IdWager]  = D.[IdWager] AND 
      D.[IdGame]   = G.[IdGame]  AND 
      H.[IdAgent]  = A.[IdAgent] AND
      H.[IdPlayer] = J.[IdPlayer] AND
      H.[IdWagerType] = W.[IdWagerType] AND
      D.[IdSport] = P.[IdSport] AND
      D.[Period]  = P.[NumberOfPeriod] AND 
      H.[IdAgent] = 5585 AND 
      D.[IdWager] = 12683461 AND
      D.[RESULT]  = 1
GROUP BY J.[Player], G.[GradedDate], D.[RESULT], H.[RiskAmount], H.[WinAmount], G.[VisitorTeam], G.[HomeTeam], 
         P.[PeriodDescription], G.[VisitorNumber], G.[HomeNumber], D.[IdWager], W.[Description]
ORDER BY TIME DESC
) AS t1
) t2 WHERE rn=1
 

[adryan310]

Gracias amigo, si no es mucha la molestia podrías explicarme mejor este select que me pusistes para entenderlo?

[Libras]

investiga sobre la funcion row_number, lo que hice fue agarrar el primer registro que regresa esta funcion sobre el campo de fecha :)

[adryan310]

Gracias amigo investigare te debo otra mas