Query Assistance - pulling the latest date for each record


With the below query - I am trying to only return 1 row for each occurence of the 'tr_rowid_debtor'

I want to pull the latest only the latest 'tr_payment_date' for tr_rowid_debtor with the associated tr_to_agency and  tr_to_client

SELECT        TOP (100) PERCENT tr_rowid_debtor, tr_payment_date, tr_to_agency, tr_to_client
FROM            dbo.de_transaction
WHERE        (tr_account = 16) AND (tr_rowid_debtor < 1000)
GROUP BY tr_rowid_debtor, tr_payment_date, tr_to_agency, tr_to_client
ORDER BY tr_payment_date DESC

Open in new window

Could a distinct help this out?  
Could anyone please help?

many thanks
James MurphyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
something like this should worked:

Select a.* from
	SELECT tr_rowid_debtor, tr_payment_date, tr_to_agency, tr_to_client,
	Row_number() over (partition by tr_rowid_debtor, tr_to_agency order by tr_payment_date desc) idx
	FROM dbo.de_transaction
	WHERE tr_account = 16 AND tr_rowid_debtor < 1000
	GROUP BY tr_rowid_debtor, tr_payment_date, tr_to_agency, tr_to_client
) a
Where a.idx = 1
ORDER BY a.tr_payment_date DESC

Open in new window

James MurphyAuthor Commented:

It is still bring out duplicate's:

this is part of the results:
tr_rowid_debtor    tr_payment_date    tr_to_agency    tr_to_client    idx
403      2016-06-01      -77.5500      0.0000      1
403      2016-06-08      -9.5600      0.0000      1
404      2016-06-01      -51.2200      0.0000      1
405      2016-06-01      -20.2700      0.0000      1
405      2016-06-02      -6.9200      0.0000      1
406      2016-06-01      -29.9900      0.0000      1

the first 2 records returned have the same tr_rowid_debtor.  should it be using max or something in the select clause?
Ryan ChongSoftware Team LeadCommented:
as you have mentioned to group your records based on tr_rowid_debtor and tr_to_agency ?

or i could be wrong... you may try this instead:

Row_number() over (partition by tr_rowid_debtor order by tr_payment_date desc) idx

Open in new window

see if you get expected results now?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James MurphyAuthor Commented:
That is perfect!  many thanks!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.