SQL Query to Display Max value in each category

Question

0.00/5 (No votes)

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

PLease help me to write SQL Query

Thanks
Mohan

Posted 27-Jul-14 23:30pm

V Mohan

Updated 28-Jul-14 0:08am

Aarti Meswania

v2

Add a Solution

Comments

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

Thanks in advance

4 solutions

Solution 1

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

Posted 28-Jul-14 0:12am

Abhinav S

v2

Comments

V Mohan 28-Jul-14 6:19am

Can you please help with this

Maciej Los 28-Jul-14 7:33am

Good point, 5!

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

Posted 28-Jul-14 1:33am

Maciej Los

Comments

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;

Posted 28-Jul-14 0:58am

Mahender Sharma

Comments

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.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2014-07-28T00:25:00

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!
:)

Posted 28-Jul-14 0:25am

Aarti Meswania

Comments

V Mohan 28-Jul-14 6:36am

Thank you so much. Its working fine

Aarti Meswania 28-Jul-14 6:49am

welcome :)
Glad to help you! :)

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 :)

SQL Query to Display Max value in each category

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0

SQL Query to Display Max value in each category

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us