arrange as per group by

Question

1.00/5 (2 votes)

See more:

--scenario

i have 10 values like this

590
800
800
289
800
590
478
123
800
600

i want the op like this

800
590
289
478
123
600

Like the most count comes first and so on

Posted 2-Aug-13 0:52am

anurag19289

Add a Solution

Comments

Maciej Los 2-Aug-13 7:31am

Do you mean you want to fetch only unique values?

anurag19289 2-Aug-13 9:09am

i want unique
next thing is the number whose count is more it should come first..and so on

like if i have input

12
12
12
12
15
15
10
10
10

then op should be
12
10
15

3 solutions

Solution 1

XML

List<int> list = new List<int>();
list.Add(590);
list.Add(800);
list.Sort();

Posted 2-Aug-13 1:18am

Jean A Brandelero

Comments

anurag19289 2-Aug-13 9:38am

i want to do it in backend

Solution 3

I got the solution after some analysis... but it seems i am doing little complicated... guide me if you get optimized code...

SQL

create table #tempcount(amount int)
insert into #tempcount (amount)
select 1
union all select 2
union all select 2
union all select 2
union all select 2
union all select 2
union all select 5
union all select 5
union all select 5

create table #tempfinal(row int, amount int)

SQL

insert into #tempfinal
select row_number() over (partition by amount order by amount) as [row number],
amount
from #tempcount


select amount from #tempfinal
group by amount
having amount >0
order by max(row) desc

Posted 2-Aug-13 2:03am

anurag19289

Comments

Maciej Los 2-Aug-13 8:19am

Is it an answer or is it another question?

anurag19289 2-Aug-13 9:10am

its an answer

Adarsh chauhan 2-Aug-13 8:24am

i think you want to do something like this..

insert into #tempfinal
select row_number() over ( order by amount) as [row number],
amount
from #tempcount
group by amount

Adarsh chauhan 2-Aug-13 8:28am

But i didn't get the logic, why you want to do something like this..Don't you think you are making your code complicated?? Maciej has already given nice answer. just a little addition for rowno need to be added to that for your new requirement

anurag19289 2-Aug-13 9:22am

Adarsh Maciej has given a good answer. But that will not work if i want the number whose occurencce is more will come first[like 800].

Requiremnt is something ike: i have two drop down.like example in dropdown1

1)facility12)facility23)facility3under facility1 we have so many claim amount

1)8002)8003)8004)8005)4006)4007)6)400 6008)600

similarly for facility2 some values and for facility3 some values...

so as soon as i select facililty1 in dropdown1 in dropdown2 it will be like800 400

600

And then search button is there ... as per the list populated in dropdown2... we select.. and manipulate the things,,,, i hope you got it what i am looking for,.,, for this dropdown2 ..i am working ,,, so i want some simle query in back end which will do my purpose

Like in dropdown2 the number which comes in top...its and indication to the user that under facility1 ,,,,the maximum number of employees are claiming 800 rs
then 400 rs
then 600 rs

anurag19289 2-Aug-13 9:40am

yes Maciej is correct....

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 · Accepted Answer · 2013-08-02T01:32:00

Please, read my comment first.

If you want to fetch only unique values, please, try this:

SQL

DECLARE @tmp TABLE (MyNumber INT)

INSERT INTO @tmp (MyNumber)
SELECT 590
UNION ALL SELECT 800
UNION ALL SELECT 800
UNION ALL SELECT 289
UNION ALL SELECT 800
UNION ALL SELECT 590
UNION ALL SELECT 478
UNION ALL SELECT 123
UNION ALL SELECT 800
UNION ALL SELECT 600

--display distinct values
SELECT DISTINCT MyNumber
FROM @tmp

SELECT MyNumber, COUNT(MyNumber) AS CountOfMyNumber
FROM @tmp
GROUP BY MyNumber
--uncomment below line if you want to show only non-duplicated values
--HAVING COUNT(MyNumber)=1
--unocmment below line if you would like to order by number of items
--ORDER BY COUNT(MyNumber) DESC

Frist query result set:

MyNumber
123
289
478
590
600
800

Second query result set:

MyNumber	CountOfMyNumber
123		1
289		1
478		1
590		2
600		1
800		4

arrange as per group by

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us