select distinct


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: select distinct

  1. #1
    erwin Guest

    select distinct


    I have following problem with a SELECT DISTINCT statement.

    SELECT distinct r.inc||'~'||r_FDT||'~||r.EL
    WHERE ( r.PROJECT='CLS2SR'
    AND r.LV='P');

    The output looks like

    01~BL~FDT1

    I use the DISTINCT option here because I have 5 matches for this selection.
    My problem is that I need to get this number ( 5 ) in the output so that
    it would
    look like

    01~BL~FDT1~5

    I tried following statement :
    SELECT distinct r.inc||'~'||r_FDT||'~||r.EL,COUNT(*)
    WHERE ( r.PROJECT='CLS2SR'
    AND r.LV='P');

    But here I get compiler errors.

    How can I solve this.

  2. #2
    Simon Sellick Guest

    Re: select distinct


    "erwin" <erwin.van-trier@alcatel.com> wrote:
    >
    >I have following problem with a SELECT DISTINCT statement.
    >
    >SELECT distinct r.inc||'~'||r_FDT||'~||r.EL
    >WHERE ( r.PROJECT='CLS2SR'
    > AND r.LV='P');
    >
    >The output looks like
    >
    >01~BL~FDT1
    >
    >I use the DISTINCT option here because I have 5 matches for this selection.
    >My problem is that I need to get this number ( 5 ) in the output so that
    >it would
    >look like
    >
    >01~BL~FDT1~5
    >
    >I tried following statement :
    >SELECT distinct r.inc||'~'||r_FDT||'~||r.EL,COUNT(*)
    >WHERE ( r.PROJECT='CLS2SR'
    > AND r.LV='P');
    >
    >But here I get compiler errors.
    >
    >How can I solve this.


    I think you need to use GROUP BY:

    SELECT
    r.Inc [etc...] Count(*)
    FROM
    [table] r
    WHERE
    [where clause...]
    GROUP BY
    r.Inc [etc...as for select clause]

    Hope this helps.
    Simon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center