Can I group record with multiple value

Question

0.00/5 (No votes)

See more:

Hi,

I am trying to work on the statement below to group the record by the fist column which is "OPC" it didn't work on my side.

Item OPC Code1 Code2 Value Desc

1 8521 M1I1 8518 712 96892733235
2 8521 M1I1 8518 58 96899130633
3 8521 M1I1 8518 202 97022222222
1 8520 M2I1 8514 900 96897015984
1 8519 DIN1 9174 653 99990740
2 8519 DIN1 8629 120 736329982
1 8518 RIN1 9329 3600 9995957175

Result should be
Item OPC Code1 Code2 Value Desc

1 8521 M1I1 8518 712 96892733235
1 8520 M2I1 8514 900 96897015984
1 8519 DIN1 9174 653 99990740
1 8518 RIN1 9329 3600 9995957175

My Select Statement

SQL

Select Item,OPC,Code1,Code2,Value,Desc
from #tmp2 c
where c.opc =  (select max(d.opc) from #tmp2  d
                       where c.opc = d.opc and c.items = d.items
                         and  c.code1 = d.code1 and c.code2 = d.code2
                         and c.value = d.value and c.desc = d.desc)

My SQL doesn't work.

Thank in advance

Posted 24-Apr-10 19:25pm

dabuskol

Add a Solution

3 solutions

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)

Abhinav S · Answer 1 · 2010-04-24T19:39:00

Solution 1

You can use group by to group on more than one column.

Posted 24-Apr-10 19:39pm

Abhinav S

dabuskol · Answer 2 · 2010-04-24T21:24:00

SQL

Select Item,OPC,Code1,Code2,Value,Desc
from #tmp2 cwhere c.opc =  (select max(d.opc) from #tmp2  d                       where c.opc = d.opc and c.items = d.items                         and  c.code1 = d.code1 and c.code2 = d.code2                         and c.value = d.value and c.desc = d.desc)Group by Item,OPC,Code1,Code2,Value,Desc

That is my solution I just forgot to include it.

The same wrong answer, I couldn't get the grouping that I was listed above.

dabuskol · Answer 3 · 2010-04-24T23:32:00

SQL

Select Item,OPC,Code1,Code2,Value,Desc
from #tmp2 c
where c.item =  (select max(d.item) from #tmp2  d                       where c.opc = d.opc)

IT works. Thanks

New Result, The user wants to get the highest Items.

C++

3 8521 M1I1 8518 202 97022222222
1 8520 M2I1 8514 900 96897015984
2 8519 DIN1 8629 120 736329982
1 8518 RIN1 9329 3600 9995957175

Can I group record with multiple value

3 solutions

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0