How to sort

30 Jun 2006 2:11 PM

Faye

Here is my select statement. I would like to sort the result so that
they will list by the freqency of the available data for each column.

SELECT ABA_NR , SUM(CASE YEAR(SURV_YY_DT) WHEN 2006 THEN 1 ELSE 0 END)
AS c2006,
    SUM(CASE YEAR(SURV_YY_DT) WHEN 2005 THEN 1 ELSE 0 END) AS c2005,
    SUM(CASE YEAR(SURV_YY_DT) WHEN 2004 THEN 1 ELSE 0 END) AS c2004,
    SUM(CASE YEAR(SURV_YY_DT) WHEN 2003 THEN 1 ELSE 0 END) AS c2003,
    SUM(CASE YEAR(SURV_YY_DT) WHEN 2002 THEN 1 ELSE 0 END) AS c2002,
    SUM(CASE YEAR(SURV_YY_DT) WHEN 2001 THEN 1 ELSE 0 END) AS c2001
FROM FLT_OFFCR_SLRY_SURV_DTL
GROUP BY ABA_NR

Here is the example of what I want:

ABA_NR    c2006      c2005      c2004     c2003       c2002     c2001
1111111      5            4        7         5         1         5
2222222      5            6        5         1         2         4
3333333      3            0        4         4         8         9
4444444      8            7        9         0         8         1
5555555      0            0        7         8         4         4
6666666      0            0        0         0         0         0
7777777      0            0        0         0         0         0
8888888      0            0        0         0         0         0
....
Thank you.

Faye

30 Jun 2006 2:17 PM

Tracy McKibben

Faye wrote:
Show quote

> Here is my select statement. I would like to sort the result so that
> they will list by the freqency of the available data for each column.
>
> SELECT ABA_NR , SUM(CASE YEAR(SURV_YY_DT) WHEN 2006 THEN 1 ELSE 0 END)
> AS c2006,
>     SUM(CASE YEAR(SURV_YY_DT) WHEN 2005 THEN 1 ELSE 0 END) AS c2005,
>     SUM(CASE YEAR(SURV_YY_DT) WHEN 2004 THEN 1 ELSE 0 END) AS c2004,
>     SUM(CASE YEAR(SURV_YY_DT) WHEN 2003 THEN 1 ELSE 0 END) AS c2003,
>     SUM(CASE YEAR(SURV_YY_DT) WHEN 2002 THEN 1 ELSE 0 END) AS c2002,
>     SUM(CASE YEAR(SURV_YY_DT) WHEN 2001 THEN 1 ELSE 0 END) AS c2001
> FROM FLT_OFFCR_SLRY_SURV_DTL
> GROUP BY ABA_NR
>
> Here is the example of what I want:
>
> ABA_NR    c2006      c2005      c2004     c2003       c2002     c2001
> 1111111      5            4        7         5         1         5
> 2222222      5            6        5         1         2         4
> 3333333      3            0        4         4         8         9
> 4444444      8            7        9         0         8         1
> 5555555      0            0        7         8         4         4
> 6666666      0            0        0         0         0         0
> 7777777      0            0        0         0         0         0
> 8888888      0            0        0         0         0         0
> ...
> Thank you.
>
> Faye
>

Have you tried the ORDER BY clause?

30 Jun 2006 2:37 PM

Arnie Rowland

Your desired output appears sorted 'ORDER BY' the [ABA_NR] column. If that
is correct, then add

ORDER BY ABA_NR

How else would you want this data sorted?

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Tracy McKibben" <tr***@realsqlguy.com> wrote in message
news:OEPY5$EnGHA.2264@TK2MSFTNGP04.phx.gbl...
> Faye wrote:
>> Here is my select statement. I would like to sort the result so that
>> they will list by the freqency of the available data for each column.
>>
>> SELECT ABA_NR , SUM(CASE YEAR(SURV_YY_DT) WHEN 2006 THEN 1 ELSE 0 END)
>> AS c2006,
>> SUM(CASE YEAR(SURV_YY_DT) WHEN 2005 THEN 1 ELSE 0 END) AS c2005,
>> SUM(CASE YEAR(SURV_YY_DT) WHEN 2004 THEN 1 ELSE 0 END) AS c2004,
>> SUM(CASE YEAR(SURV_YY_DT) WHEN 2003 THEN 1 ELSE 0 END) AS c2003,
>> SUM(CASE YEAR(SURV_YY_DT) WHEN 2002 THEN 1 ELSE 0 END) AS c2002,
>> SUM(CASE YEAR(SURV_YY_DT) WHEN 2001 THEN 1 ELSE 0 END) AS c2001
>> FROM FLT_OFFCR_SLRY_SURV_DTL
>> GROUP BY ABA_NR
>>
>> Here is the example of what I want:
>>
>> ABA_NR    c2006      c2005      c2004     c2003       c2002     c2001
>> 1111111      5            4        7         5         1         5
>> 2222222      5            6        5         1         2         4
>> 3333333      3            0        4         4         8         9
>> 4444444      8            7        9         0         8         1
>> 5555555      0            0        7         8         4         4
>> 6666666      0            0        0         0         0         0
>> 7777777      0            0        0         0         0         0
>> 8888888      0            0        0         0         0         0
>> ...
>> Thank you.
>>
>> Faye
>>
>
> Have you tried the ORDER BY clause?