Group Sort By Expression

What is the syntax to sort on a group. For example:
If group = 1 then sort by field1 ascending
If group = 2 then sort by field2 ascending
If group = 3 then sort by field3 descending
Thanks!There are a couple ways You could write this.
If your "Group" selection is a integer:
=Choose(Parameters!Group.Value,Fields!Field1.Value,
Fields!Field2.Value,Fields!Fields3.Value)
Otherwise
=IIf(Parameters!Group.Value ='1',Fields!Fields1.Value,IIf(Parameters!Group.Value ='2',Fields!Field2.Value,Fields!Field3.Value))
Michael C
"Anonymous" wrote:
> What is the syntax to sort on a group. For example:
> If group = 1 then sort by field1 ascending
> If group = 2 then sort by field2 ascending
> If group = 3 then sort by field3 descending
> Thanks!|||What is the syntax though for desc vs asc.
"Michael C" wrote:
> There are a couple ways You could write this.
> If your "Group" selection is a integer:
> =Choose(Parameters!Group.Value,Fields!Field1.Value,
> Fields!Field2.Value,Fields!Fields3.Value)
> Otherwise
> =IIf(Parameters!Group.Value => '1',Fields!Fields1.Value,IIf(Parameters!Group.Value => '2',Fields!Field2.Value,Fields!Field3.Value))
> Michael C
> "Anonymous" wrote:
> > What is the syntax to sort on a group. For example:
> >
> > If group = 1 then sort by field1 ascending
> > If group = 2 then sort by field2 ascending
> > If group = 3 then sort by field3 descending
> >
> > Thanks!|||On Aug 22, 12:32 pm, Anonymous <Anonym...@.discussions.microsoft.com>
wrote:
> What is the syntax to sort on a group. For example:
> If group = 1 then sort by field1 ascending
> If group = 2 then sort by field2 ascending
> If group = 3 then sort by field3 descending
> Thanks!
Right click the table/matrix control and select Properties. Select the
Groups tab, then select the 'Edit...' button for the Groups to set
sorting on. Then select the 'Sorting' tab and below 'Expression,'
select 'Fields!Field1.Value' and then below 'Direction,' select
'ascending.' Repeat this for the other groups as well. Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Group by and Case Statements

Hi,
I have a query where I am using a case statement and a group by e.g.
Select Field1, Field2, Field3, Max(Field4),
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as Field4
WHERE <something>
GROUP BY Field1, Field2, Field3, Field4
The problem I am having is the group by isn't using the value from
the Case Statement, it's not grouping the records correctly. Anyone
have any idea's why that would happen?
Thanks for the helpBy definition, the expressions in the GROUP BY clause reffer to the
columns from the table, not to the columns defined in the SELECT
clause. You probably need to use a subquery for this:
SELECT Field1, Field2, Field3, MAX(Field4), TheExpression as Field4
FROM (
SELECT Field1, Field2, Field3, Field4,
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as TheExpression
FROM <your table>
WHERE <something>
) x
GROUP BY Field1, Field2, Field3, TheExpression
Razvan|||Thanks, that's what I figured. I was hoping there was a more eligant
way around that.
Thanks|||Simply copy the same CASE expression used in the SELECT list into the
GROUP BY:
Select Field1, Field2, Field3, Max(Field4),
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as Field4
WHERE <something>
GROUP BY Field1, Field2, Field3,
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END
Roy Harvey
Beacon Falls, CT
On 3 May 2006 09:25:44 -0700, "Red2" <sdibello@.gmail.com> wrote:

>Hi,
> I have a query where I am using a case statement and a group by e.g.
> Select Field1, Field2, Field3, Max(Field4),
> CASE
> WHEN <something> THEN ' '
> WHEN <something> THEN 1
> END as Field4
> WHERE <something>
> GROUP BY Field1, Field2, Field3, Field4
> The problem I am having is the group by isn't using the value from
>the Case Statement, it's not grouping the records correctly. Anyone
>have any idea's why that would happen?
>Thanks for the help|||Red2 wrote:
> Hi,
> I have a query where I am using a case statement and a group by e.g.
> Select Field1, Field2, Field3, Max(Field4),
> CASE
> WHEN <something> THEN ' '
> WHEN <something> THEN 1
> END as Field4
> WHERE <something>
> GROUP BY Field1, Field2, Field3, Field4
> The problem I am having is the group by isn't using the value from
> the Case Statement, it's not grouping the records correctly. Anyone
> have any idea's why that would happen?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
It'd be better if you showed the actual query. Your example has a
Max(Field4) and the CASE expression is labeled as Field4. If the GROUP
BY clause has Field4 is will be grouping the Field4 in the Max()
function. Obviously, not what you want to do. Is this a misnomer or
are you really using the same column in the Max() function and as the
CASE expression's name?
If you want to GROUP BY the result of a CASE expression you have to
include the CASE expression in the GROUP BY clause:
GROUP BY field1, field2, field3, CASE WHEN <something> THEN X ELSE Y END
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRFkUgYechKqOuFEgEQLZ2ACdHDbb0StLG/MEkaOpME4KfeDYTfcAn1I2
EEZOhQ/brevVe0gSJNujPhER
=aD2k
--END PGP SIGNATURE--

GROUP BY alias...possible?

Hello,
I have a statement as follows:
SELECT field1, field2, {expression} as calc1
FROM table1
GROUP BY field1, calc1
The absolutely critical thing to this statement is that I need to group
by calc1, but I get an 'Invalid Column Name' error. However, to get the
results I need I have to do a calculation as the data required is not
available in that form in the table, and I also have to be able to
group by it too. I'm sure someone must have come across this problem
and found a workaround?Well, one way to do this is to use calculation in the group by:
select
col1-col2 as Diff,
col3
from
table1
group by
(col1-col2),
col3
Is this good enough or you really need to use the same name?
MC
<champ.supernova@.gmail.com> wrote in message
news:1133343294.033508.241990@.o13g2000cwo.googlegroups.com...
> Hello,
> I have a statement as follows:
> SELECT field1, field2, {expression} as calc1
> FROM table1
> GROUP BY field1, calc1
> The absolutely critical thing to this statement is that I need to group
> by calc1, but I get an 'Invalid Column Name' error. However, to get the
> results I need I have to do a calculation as the data required is not
> available in that form in the table, and I also have to be able to
> group by it too. I'm sure someone must have come across this problem
> and found a workaround?
>|||champ.supernova@.gmail.com wrote on 30 Nov 2005 01:34:54 -0800:

> Hello,
> I have a statement as follows:
> SELECT field1, field2, {expression} as calc1
> FROM table1
> GROUP BY field1, calc1
> The absolutely critical thing to this statement is that I need to group
> by calc1, but I get an 'Invalid Column Name' error. However, to get the
> results I need I have to do a calculation as the data required is not
> available in that form in the table, and I also have to be able to
> group by it too. I'm sure someone must have come across this problem
> and found a workaround?
You can use {expression} in the group by instead of calc1 (just copy the
expression from the select), or use a subquery, or use DISTINCT. However,
you've still got a problem - you can't have field2 in your select list if
you're not grouping by it. If you don't need that field, remove it from the
select, if you do then add it to your group by. Assuming that you don't want
it, here's a couple of ideas:
SELECT field1, {expression} as calc1
FROM table1
GROUP BY field1, {expression}
SELECT DISTINCT field1, {expression} as calc1
FROM table1
SELECT field1, calc1
FROM
(SELECT field1, {expression} as calc1
FROM table1) As A
GROUP BY field1, calc1
SELECT DISTINCT field1, calc1
FROM
(SELECT field1, {expression} as calc1
FROM table1) As A
Dan|||Thanks guys, MC's answer solved it. Much obliged.