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

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

dabuskol · Answer 1 · 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

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.

Abhinav S · Answer 3 · 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

Can I group record with multiple value

3 solutions

Solution 4

Solution 3

Solution 1

Add your solution here

Preview 0

Can I group record with multiple value

3 solutions

Solution 4

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us