Only Unique Data required of a table. Please help with this Query.

[ankitmathur]

Hi Friends,

I've been looking for an answer to this problem for quite sometime but an unable to find one. I hope someone from this group can help me guide to the right way.

Here's my SQL Query:

SELECT AmtAfter, AcctID FROM Usr_AmtDetails Order By AcctRecharged

[b][u]Data Retrieved by the Query:[/u][/b]
 
[b]AmtAfter AcctId[/b]
2006.12 000280196
4006.12 000280196
0.00 002101839
1000.55 002107518
1001.24 002107518
1012.92 002107518
1242.25 002107518
996.53 002107518
999.49 002107518
1590.02 002107582
 
[b][u]Data I want:[/u][/b]
 
[b]AmtAfter AcctId[/b]
2006.12 000280196
0.00 002101839
1000.55 002107518
1590.02 002107582

Basically, I want only the latest entry of an distinct accountids & not the rest of the records also.Can someone guide me to a solution.

Thanks
Ankit Mathur

[Jim Doherty]

Hi Friends,

I've been looking for an answer to this problem for quite sometime but an unable to find one. I hope someone from this group can help me guide to the right way.

Here's my SQL Query:

SELECT AmtAfter, AcctID FROM Usr_AmtDetails Order By AcctRecharged

 
[b][u]Data Retrieved by the Query:[/u][/b]
 
[b]AmtAfter AcctId[/b]
2006.12 000280196
4006.12 000280196
0.00 002101839
1000.55 002107518
1001.24 002107518
1012.92 002107518
1242.25 002107518
996.53 002107518
999.49 002107518
1590.02 002107582
 
[b][u]Data I want:[/u][/b]
 
[b]AmtAfter AcctId[/b]
2006.12 000280196
0.00 002101839
1000.55 002107518
1590.02 002107582

Basically, I want only the latest entry of an distinct accountids & not the rest of the records also.Can someone guide me to a solution.

Thanks
Ankit Mathur

What is 'AcctRecharged' is a 'date' field datatype that can be used to determine the latest entry?

Jim

[ankitmathur]

No Jim,

That is actually a varchar value actually the same as AcctId.

We have ModifiedDate though.

What is 'AcctRecharged' is a 'date' field datatype that can be used to determine the latest entry?

Jim

[ck9663]

No Jim,

That is actually a varchar value actually the same as AcctId.

it looks like you just want to get the first record. regardless if the second record has a bigger or smaller value...my suggestion would be use the row_number() function and get the min(row_number( )) group by your AcctId...then do another subquery to joine the resulting row number of the above query just to get the AcctReCharged field.

-- ck

[ankitmathur]

You got it absolutely right CK.

I want only the latest record of an account regardles of it being larger than or, lesser than other values.

However, I couldn't understand your idea about ROW_NUMBER() .

Can you brief a lil' more on that to me.

Thanks
Ankit Mathur

it looks like you just want to get the first record. regardless if the second record has a bigger or smaller value...my suggestion would be use the row_number() function and get the min(row_number( )) group by your AcctId...then do another subquery to joine the resulting row number of the above query just to get the AcctReCharged field.

-- ck

[ck9663]

sure. you can read here

-- ck

[ankitmathur]

Hi CK,

I had gone through the link you redirected me to.
While it looked like the thing that's going to work for me unfortunately I'm on SQL 2000 & 'ROW_NUMBER' is not a recognized function name in MSSQL 2000.

Can you suggest me a workaround for this in MSSQL 2000

Thanks
Ankit

sure. you can read here

-- ck

[ck9663]

Hi CK,

I had gone through the link you redirected me to.
While it looked like the thing that's going to work for me unfortunately I'm on SQL 2000 & 'ROW_NUMBER' is not a recognized function name in MSSQL 2000.

Can you suggest me a workaround for this in MSSQL 2000

Thanks
Ankit

is there some sort of InsertedDate or any key that sequentially define the records on your table? if none? could you just pick one of the rows, maybe the one with smaller or bigger AcctRecharged ?

-- ck

[Jim Doherty]

You got it absolutely right CK.

I want only the latest record of an account regardles of it being larger than or, lesser than other values.

However, I couldn't understand your idea about ROW_NUMBER() .

Can you brief a lil' more on that to me.

Thanks
Ankit Mathur

That is why I asked you for something that determines what the the 'latest' is and by that I was referring to some mechanism whether it be a date field that has the time in it (or any other field so that you could pinpoint what is logically the latest. You mentioned 'DateModified' Now is that date modified for the 'record' or does datemodified mean in effect that you can use that to determine the latest amtafter value. I trust I am making myself clear I do not know your system!

Anyhow... going on datemodified (which I am a little uncomfortable with not having confirmation from you on whether this can be used or not in the context I am meaning here).....the following SQL will return the LATEST datetime modified for the AccID concerned and thus the relevant AmtAfter you require for each ActID. The order by clause is simply on ActID but ou can amend that to suit your needs obviously.

 
SELECT TOP 100 PERCENT Usr_AmtDetails.No, Usr_AmtDetails.AmtAfter, 
	Usr_AmtDetails.ActID, Usr_AmtDetails.DateModified
FROM dbo.Usr_AmtDetails INNER JOIN
		(SELECT Usr_AmtDetails.ActID, MAX(Usr_AmtDetails.DateModified) 
		 AS MaxOfDateModified
	 FROM Usr_AmtDetails
	 GROUP BY Usr_AmtDetails.ActID) t ON 
	dbo.Usr_AmtDetails.DateModified = t .MaxOfDateModified AND 
	dbo.Usr_AmtDetails.ActID = t .ActID
ORDER BY Usr_AmtDetails.ActID

Jim :)

[ck9663]

amen to that jim ;)

-- ck