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 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.

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!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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