# Need to pick up the SUM OF Tax Amount for the highest Sequence number Per Year, Per SSN, Per employer

Consider Employee table:

```
Employerid ssn year Seqnumber q1taxamt q2taxamt q3taxamt q4taxamt
1004 101 2013 1 2000 0 0 0
1004 101 2013 2 2000 100 0 0
1004 101 2013 3 2000 100 200 0
1004 101 2013 4 2000 100 200 300
1004 102 2013 1 3000 0 0 0
1004 102 2013 2 3000 200 0 0
1004 102 2013 3 3000 200 300 0
1004 102 2013 4 3000 200 300 400
1004 102 2013 5 3000 200 300 400
```

Here the transformation rule is we need to pick the highest Seqnumber with respect to each ssn per year per

Employerid and the amounts.

i.e for 10004 for sum(q1taxamt) is 2000 +3000 = 5000

The Logic is ssn 101 has highest seq number of 4 and ssn 102 has highest seq number of 5 so we need to pick those values wrt to employerid

Example:

Want to check for q1taxamt: 2000 +3000 = 5000

Want to check for q4taxamt: 300 +400 = 700

output must be:

```
Employerid YEAR q1taxamt q2taxamt q3taxamt q4taxamt
10004 2013 5000 300 500 700
```

The below query is generating wrong result:

```
Select
Sum(E1.q1taxamt) q1taxamt,
Sum(E1.q2taxamt) q2taxamt,
Sum(E1.q3taxamt) q3taxamt,
Sum(E1.q4taxamt) q4taxamt,
E1.Employerid,
E1.YEAR
from Employee E1
join
(
select
E.Employerid,
MAX(E.seqnumber) seqnumber,
E.YEAR
from Employee E
group by E.Employerid,E.SSn,E.year
)E2
on E1.Employerid=E2.Employerid
AND E1.YEAR=E2.YEAR
and E1.seqnumber=E2.Taxseqnumber
```

### 2 Solutions collect form web for “Need to pick up the SUM OF Tax Amount for the highest Sequence number Per Year, Per SSN, Per employer”

Just use `row_number()`

:

```
select e.*
from (select e.*,
row_number() over (partition by E.Employerid, E.SSn, E.year
order by e.seqnumber desc
) as seqnum
from Employee e
) e
where seqnum = 1;
```

For best performance, you want an index on `Employee(EmployerId, SSN, seqnumber desc)`

.

You missing `SSN`

join predicate between `E1`

and `E2`

thats why you are getting wrong result. I think this might be faster than `Row_Number`

method.

```
Select
Sum(E1.q1taxamt) q1taxamt,
Sum(E1.q2taxamt) q2taxamt,
Sum(E1.q3taxamt) q3taxamt,
Sum(E1.q4taxamt) q4taxamt,
E1.Employerid,
E1.YEAR
from Employee E1
join
(
select
E.Employerid,
E.SSn,
MAX(E.seqnumber) seqnumber,
E.YEAR
from Employee E
group by E.Employerid,E.SSn,E.year
)E2
on E1.Employerid=E2.Employerid
AND E1.YEAR=E2.YEAR
AND E1.SSN = E2.SSN --Here
and E1.seqnumber=E2.Taxseqnumber
```