12,397,805 members (54,779 online)
Rate this:
See more:
Hi....I have Find last record in each ID records.

```ID	SID	Amount	Pay	Due
1	1	10000	2000	8000
2	2	10000	3000	7000
3	3	10000	5000	5000
4	1	10000	3000	5000
5	2	10000	5000	2000
6	3	10000	2000	3000
7	1	10000	1000	4000
8	2	10000	1000	1000
```

For Eg:Out Put.
```SID Due
1   4000
2   1000
3   3000```
Posted 18-Mar-13 2:06am
Abhai Oza1.6K
Updated 18-Mar-13 2:16am
Maciej Los221.8K
v2

Rate this:

## Solution 2

Try this:
```SELECT t1.SID, t1.DUE
FROM YourTable AS t1 RIGHT JOIN (
SELECT SID, MAX(ID) AS ID
FROM YourTable
GROUP BY SID) AS t2 ON t1.SID =t2.SID AND t1.ID=t2.ID
ORDER BY t1.SID```
v3
Achal Oza 18-Mar-13 8:32am

Ambiguous column name 'SID'.
Maciej Los 18-Mar-13 8:34am

see updated solution ;)
CHill60 18-Mar-13 10:40am

My 5 to get this (correct) solution above the earlier truly awful one
Maciej Los 18-Mar-13 10:41am

Thank you, Chill60 ;)
Rate this:

## Solution 1

Hi,

Check the following script

```DECLARE @AmtDtls TABLE(ID INT,SID INT, Amount NUMERIC(9,0), Pay NUMERIC(9,0), Due NUMERIC(9,0))
INSERT INTO @AmtDtls (ID, SID, Amount, Pay , Due )
SELECT 1, 1, 10000, 2000, 8000
UNION ALL
SELECT 2, 2, 10000, 3000, 7000
UNION ALL
SELECT 3, 3, 10000, 5000, 5000
UNION ALL
SELECT 4, 1, 10000, 3000, 5000
UNION ALL
SELECT 5, 2, 10000, 5000, 2000
UNION ALL
SELECT 6, 3, 10000, 2000, 3000
UNION ALL
SELECT 7, 1, 10000, 1000, 4000
UNION ALL
SELECT 8, 2, 10000, 1000, 1000

SELECT A.SID, A.Due
FROM @AmtDtls A
INNER JOIN (SELECT MAX(ID) ID, SID FROM @AmtDtls GROUP BY SID) T ON T.ID=A.ID
ORDER BY A.SID
```
Regards,
GVPrabu
Achal Oza 18-Mar-13 8:25am

But I have around 56000 record..
So many time wait for perfact Output..
gvprabu 18-Mar-13 8:29am

You have Index for SID and ID Column for that table?

Top Experts
Last 24hrsThis month
 OriginalGriff 380 Richard Deeming 244 Richard MacCutchan 200 Karthik Bangalore 190 ppolymorphe 100
 OriginalGriff 6,508 Karthik Bangalore 2,572 ppolymorphe 2,480 F-ES Sitecore 1,977 Richard MacCutchan 1,952