13,145,068 members (57,036 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.7K
Updated 18-Mar-13 2:16am
Maciej Los255.6K
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 440 Dave Kreskowiak 205 Richard Deeming 195 Richard MacCutchan 110 RickZeeland 100
 OriginalGriff 5,650 Graeme_Grant 5,051 ppolymorphe 1,884 Jochen Arndt 1,834 CPallini 1,625