quote:

---

Originally posted by Peso

SELECT	hmer_xp,
	id_xp,
	id_ontotita
FROM	(
		SELECT	hmer_xp,
			id_xp,
			id_ontotita,
			row_number() over (partition by id_ontotita order by hmer_xp desc) as recid
		FROM	ONTOT_xreopistwseis
		WHERE	hmer_xp <= '2009-04-16'
			AND b_canceled = 0
	) AS d
WHERE	recID = 1

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by ymamalis

hi i have this query
SELECT TOP (100) PERCENT MAX(hmer_xp) AS MaxOfhmer_xp, max(id_xp) AS MaxOfMaxOfid_xp, id_ontotita AS id_ont
FROM ONTOT_xreopistwseis AS xp
WHERE (hmer_xp <= CONVERT(DATETIME, '2009-4-16 00:00:00', 102)) AND (b_canceled = 0) GROUP BY id_ontotita

my problem is that when i have 2 records for a specific id_ont with hmer_xp='2009-4-16 04:00:00' , id_xp = 17974 and
hmer_xp='2009-4-16 05:00:00' , id_xp = 17927 , it returns me the id_xp=17974 but i want to return me the id of the maximum date field (hmer_xp) . can you help me please?

---

SELECT     TOP (100) PERCENT xp.hmer_xp, xp.id_xp AS MaxOfMaxOfid_xp, xp.id_ontotita AS id_ont
  FROM          ONTOT_xreopistwseis AS xp
INNER JOIN (SELECT MAX(hmer_xp) AS MaxOfhmer_xp,id_ontotita
            FROM ONTOT_xreopistwseis
            GROUP BY id_ontotita) xp1
ON xp1.id_ontotita=xp.id_ontotita
AND xp1.MaxOfhmer_xp=xp.hmer_xp
WHERE   xp.hmer_xp <= CONVERT(DATETIME, '2009-4-16 00:00:00', 102)) AND xp.b_canceled = 0