how to get frequently repeated values in sql field..

Question

1.00/5 (1 vote)

See more:

I have one table i want to take maximum times repeated value in dcr and leave both column..pls tell me very urgent

type	fscode		cr	leave	flag	
1		MSR042		2	2		0
1		MSR020		2	2		0
3		ZSM010		25	25		0
1		MSR086		2	2		0
1		MSR229		2	2		0
1		MSR150		2	2		0
1		MSR088		2	2		0
1		MSR258		2	2		0
2		ASM018		2	2		0
1		MSR228		2	2		0
1		MSR074		2	2		0
1		MSR241		2	2		0
1		MSR187		2	2		0
2		ASM008		2	2		0
2		ASM014		2	2		0
1		MSR105		2	2		0
1		MSR050		2	2		0
1		MSR256		2	2		0
1		MSR212		2	2		0
1		MSR159		2	2		0
1		MSR209		7	2		0
2		ASM019		2	2		0
1		MSR237		2	2		0
1		MSR265		2	2		0
1		MSR051		5	2		0
2		ASM009		2	2		0
2		ASM028		2	2		0
1		MSR151		2	2		0
1		MSR017		2	2		0
1		MSR286		25	25		0

Posted 30-Mar-15 1:37am

Member 11337367

Updated 30-Mar-15 1:54am

Kornfeld Eliyahu Peter

v2

Add a Solution

1 solution

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)

Maciej Los · Answer 1 · 2015-03-30T01:54:00

Solution 1

Below query returns the highest value of dcr and leave fields.

SQL

SELECT [type], MAX(dcr) AS MaxOfDcr, MAX(leave) AS MaxOfLeave
FROM TableName
GROUP BY [type]

If you want to get something else, please let me know.

[EDIT]

SQL

SELECT MAX(CountOfDcr) AS MaxCountOfDcr, MAX(CountOfLeave) AS MaxCountOfLeave
FROM(
    SELECT dcr, COUNT(dcr) AS CountOfDcr, leave, COUNT(leave) AS CountOfLeave
    FROM TableName
    GROUP BY dcr, leave
) AS T

[/EDIT]

Posted 30-Mar-15 1:54am

Maciej Los

Updated 30-Mar-15 2:18am

v4

Comments

Member 11337367 30-Mar-15 8:15am

Ya,I need something else that which is maximum time repeated values in column cr,leave columns

Maciej Los 30-Mar-15 8:19am

See updated answer ;)

Member 11337367 30-Mar-15 8:31am

It will give only count of max number of times repeated vales..I want value of that

Maciej Los 30-Mar-15 8:40am

Well, use subquery only.

Member 11337367 30-Mar-15 8:42am

Can you show me how to write down..

Maciej Los 30-Mar-15 8:47am

SELECT dcr, COUNT(dcr) AS CountOfDcr, leave, COUNT(leave) AS CountOfLeave
FROM TableName
GROUP BY dcr, leave