How can get ID of record with MAX(TRX_DATE) for a particular CA_ID

[BobRoyAce]

Let's say that I have the following table:

TRXS
-------
TRX_ID
CA_ID
TRX_DATE

TRXS can have multiple records with the same CA_ID. Within the multiple records for a given CA_ID, there could be many different values for TRX_DATE. For example:

TRX_ID, CA_ID, TRX_DATE
1,1,1/3/12
2,1,1/5/12
3,1,1/10/12
4,1,2/4/12
5,1,2/3/12

As you can see, there are five records for CA_ID of 1. What I want is a query that will return the TRX_ID of the record that has the MAX(TRX_DATE) for CA_ID of 1. In the above case, the query would return a value of 4, since that record has a TRX_DATE of 2/4/12, representing the MAX value.

How can I construct a query like this?

NOTE: This question is similar to, but different from, another question that I posted to this group earlier.

[Erland Sommarskog]

Post by BobRoyAce
TRXS
-------
TRX_ID
CA_ID
TRX_DATE
TRXS can have multiple records with the same CA_ID. Within the multiple
records for a given CA_ID, there could be many different values for
TRX_ID, CA_ID, TRX_DATE
1,1,1/3/12
2,1,1/5/12
3,1,1/10/12
4,1,2/4/12
5,1,2/3/12
As you can see, there are five records for CA_ID of 1. What I want is a
query that will return the TRX_ID of the record that has the
MAX(TRX_DATE) for CA_ID of 1. In the above case, the query would return
a value of 4, since that record has a TRX_DATE of 2/4/12, representing
the MAX value.

WITH CTE AS (
SELECT TRX_ID, CA_ID, TRX_DATE,
rowno = row_number() OVER(PARTITION BY CA_ID
ORDER BY TRX_DATE DESC)
FROM TRXS
)
SELECT TRX_ID, CA_ID, TRX_DATE
FROM TRXS
WHERE rowno = 1

[b***@omegasoftwareinc.com]

Post by Erland Sommarskog
WITH CTE AS (
SELECT TRX_ID, CA_ID, TRX_DATE,
rowno = row_number() OVER(PARTITION BY CA_ID
ORDER BY TRX_DATE DESC)
FROM TRXS
)
SELECT TRX_ID, CA_ID, TRX_DATE
FROM TRXS
WHERE rowno = 1

Thanks for the reply...just had to change the last TRXS to CTE, resulting in...

WITH CTE AS (
SELECT TRX_ID, CA_ID, TRX_DATE,
rowno = row_number() OVER(PARTITION BY CA_ID
ORDER BY TRX_DATE DESC)
FROM TRXS
)
SELECT TRX_ID, CA_ID, TRX_DATE
FROM CTE
WHERE rowno = 1

[Erland Sommarskog]

Post by b***@omegasoftwareinc.com
Thanks for the reply...just had to change the last TRXS to CTE, resulting in...
WITH CTE AS (
SELECT TRX_ID, CA_ID, TRX_DATE,
rowno = row_number() OVER(PARTITION BY CA_ID
ORDER BY TRX_DATE DESC)
FROM TRXS
)
SELECT TRX_ID, CA_ID, TRX_DATE
FROM CTE
WHERE rowno = 1

That what's happens when there are no tables to test against. I am glad to
see that you were able to work out the issue on your own.

I didn't answer your other post, as I hoped that this response would serve
as a hint for that one.