Picking one type of multi record

[Former Member]

HI I Have data like this:

Order#            Order_code                  Order_Amt

123                 Code1                         100.00

123                 Code2                         100.00

123                 Code3                         100.00

123.                Code4                         100.00

Most of our orders will have only one order_code per order, but some have more than 1.Different parts require different works etc..

So in the above suppose we have a hierarchy, if there is a code1, only show that. if not a code1 in this list, then code2 is next.

How do I code this in the record select?

[abhilash_kumar]

Hi Paul,

What is the data source of the report?

If you're reporting against a Command Object, you'd need to handle this in the SQL query.

If you're reporting against tables/views, there is a way however, I prefer to know the source first.

-Abhilash

[Former Member]

It's a command.

[abhilash_kumar]

You should be able to handle this in a SQL Query like this:

Select

T2.Order#,

T2.Code,

T2.Rows

From

(

Select T.Order#,

T.Code,

Dense_Rank() Over(Partition By Order# Order By Case When Code = 'Code1' then 1

When Code = 'Code2' then 2

When Code = 'Code3' then 3

When Code = 'Code4' then 4 END ASC) Rows

from Table T

)T2

Where

T2.Rows = 1

Order by T2.Code

-Abhilash