I would like to return the first row only from an inner join. I have two tables:
TABLE_X | TABLE_Y
id | id creationdate xid
1 | 1 01/01/2011 1
2 | 2 01/01/2011 1
3 | 3 31/12/2010 2
4 | 4 28/12/2010 3
Rows in TABLE Y can have identical creation dates so I am first getting the MAX(creationdate) and then then MAX(id) from this set, for example:
SELECT a.id,
c.id,
d.id,
e.id,
d.CREATIONDATE,
a.REFNUMBER,
a.DATECREATED,
a.DESCRIPTION,
e.CATEGORYCODE,
e.OUTSTANDINGAM_MONAMT,
e.PREVPAIDAMOUN_MONAMT,
e.TOTALINCURRED_MONAMT,
e.LOSSFROMDATE,
FROM
TABLE_A a
INNER JOIN TABLE_B b ON (b.id = a.id)
INNER JOIN TABLE_C c ON (c.id = b.id)
INNER JOIN TABLE_D d ON
(
c.i =
(
select
d.id
FROM TABLE_D
WHERE TABLE_D.id = c.id
AND TABLE_D.id =
(
select
max(id)
from TABLE_D t1
where c_id = c.id
and CREATIONDATE =
(
select
max(CREATIONDATE)
from TABLE_D t2
where t2.c_id = t1.c_id
)
)
)
)
INNER JOIN TABLE_E e ON
(
d.i =
(
select
e.d_id
from TABLE_E
where d_id = d.id
AND id =
(
select
max(id)
from e t1
where e.d_id = d.id
and CREATIONDATE =
(
select
max(CREATIONDATE)
from TABLE_E t2
where t2.d_id = t1.d_id
)
)
)
)
This works when I call it on it's own but when I add it to an INNER JOIN I am getting a row for each matching row in table Y.
What I want is the latest record by creationdate and id where xid = id from TABLE_X.
Try this query
select *,(
select top 1 creationdate from Table_Y
where from Table_Y.xId = m.id
order by Table_Y.CreationDate
)
from Table_X m
The sub query will pick the top 1 result which have max creationdate and the main query will pick all the records so you have your desired result
This should do it The complex subquery works out the max date for each Y.xid group, and from that, further works out the Max Y_ID (let this represent the key on table Y)
SELECT X.*, Y.*
FROM TABLE_X X
INNER JOIN (
SELECT t1.xid, Max(t1.Y_id) MaxY_id
FROM
(SELECT t2.xid, MAX(t2.CREATIONDATE) MDate
FROM TABLE_Y t2
GROUP BY t2.xid) t
inner join TABLE_Y t1
on t.xid=t1.xid and t.MDate = t1.CREATIONDATE) MAXY
ON MAXY.xid = X.ID
INNER JOIN TABLE_Y Y
ON Y.Y_ID = MAXY.MAXY_ID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With