15,964,052 members
See more:
Hi,

I have a table like below
```ID	RefNo	Prefix	Seq
179	55	I	1
180	55	A	1
182	55	A	2
183	55	A	3
184	56	A	1
181	56	I	1
187	56	A	2
188	56	A	3
189	59	I	1
190	59	A	1```

And i need outpot from this table Based on RefNo , Prefix , seq
```ID	RefNo	Prefix	Seq
183	55	A	3
188	56	A	3
190	59	A	1```

MAx of Seq number based on prefix in RefNo field should be as output

Thanks
Mohan
Posted
Updated 28-Jul-14 0:08am
v2
Gihan Liyanage 28-Jul-14 5:46am
ID RefNo Prefix Seq
183 55 A 3
188 56 A 3
190 59 A 1

Is this the real Output You want ? Or You want Each Maximum Value for each Category ? Can you please explain it bit. and write the real output
V Mohan 28-Jul-14 5:53am
Thanks. I want Each maximum value in each category.
Flow is
1.first need to take distinct value of Refno
2.Check the Prefix for those rows and take maximum value of Prefix
3 Then in that i need to get max as seq
For above example
1.55, 56,59 is the output of first step

2 in these A is max of 55 and 56,59
3 for a 55 Seq 3 is the maximum, for 56 a 3 seq is maximum
4 for 59 a is the max value
so the output is
ID RefNo Prefix Seq
183 55 A 3
188 56 A 3
190 59 A 1

## Solution 1

Using a combination of GROUP BY and MAX should give you the right results.

v2
V Mohan 28-Jul-14 6:19am
Maciej Los 28-Jul-14 7:33am
Good point, 5!

## Solution 2

SQL
```SELECT * FROM
(
SELECT row_number() OVER (PARTITION BY RefNo order by RefNo,Prefix ,Seq DESC) srno, ID, RefNo, Prefix, Seq
from TableName
) as temp
where srno = 1```

Happy Coding!
:)

V Mohan 28-Jul-14 6:36am
Thank you so much. Its working fine
Aarti Meswania 28-Jul-14 6:49am
welcome :)
Maciej Los 28-Jul-14 7:34am
Good job, Aarti!
Glad to see you again ;)
Aarti Meswania 28-Jul-14 7:44am
thank you, Maciej Los :)

## Solution 4

As Abhinav S. had mentioned, it's possible to use `GROUP BY` clause. There is only one note: `ID` must be omitted.
SQL
```SELECT RefNo, Prefix, MAX(Seq) AS Seq
FROM TableName
GROUP BY RefNo, Prefix```

Aarti Meswania 28-Jul-14 7:46am
without ID it's good option
my method is resource and time taking if OP do not need ID then your query is best suited
Maciej Los 28-Jul-14 8:50am
Thank you, Aarti ;)
Aarti Meswania 28-Jul-14 9:50am
welcome :)

## Solution 3

SQL
```SELECT Last(Table1.ID) AS LastOfID, Table1.RefNo, Min(Table1.PreFix) AS MinOfPreFix, Max(Table1.Seq) AS MaxOfSeq
FROM Table1
GROUP BY Table1.RefNo;```

Maciej Los 28-Jul-14 7:36am
Does LAST is it built-in SQL function?
In MS SQL Server 2012 Express edition i got the message error: `'LAST' is not a recognized built-in function name.`
Mahender Sharma 28-Jul-14 23:41pm
This is MS ACCESS query and requiers chaneges accordingly
Maciej Los 29-Jul-14 1:53am
Have a look at tags, they are: `SQL, Server`. Your answer does not corresponds to them. Please, delete this answer to avoid down-voting.