Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58

    Unanswered: GROUP BY question

    If i'm selecting two columns from a table, how can i get just one of the columns to GROUP...

    EXAMPLE:

    SELECT B.Name, B.Org_Id FROM OrgN B INNER JOIN Entity O ON O.Id = B.Org_Id AND O.Stat = 95 AND O.Aer IS NOT NULL WHERE BKN.Name LIKE 'p%' GROUP BY Org_Id ORDER BY Name

    This obviously doesn't work since i don't have the name in the grouping, but in this case i don't want the name to be grouped just the org_id.

    EXAMPLE OUTPUT:
    MyName 234
    MyName 432
    Another 3423

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    As far as the above query, I do not see a reason to group by ...

    Logically it doesn't make sense to exclude a column in the GROUP BY Clause but have in the SELECT List .. consider a simple example: A table has three columns, continent, country, sex ... Now you wish to know the number of males and females, then you will do
    select sex,count(*) from table1 group by sex

    select country,sex,count(*) from table1 group by sex

    The second query will not be able to give logically correct result... Do you agree ??

    But, if you want to see the result only for Europe, you can say
    select 'Europe',country,sex,count(*) from table1 where continent='Europe' group by 'Europe',country,sex
    or to make it more generic
    select continet,country,sex,count(*) from table1 where continent='Europe' group by continent,country,sex

    So, if you add a column to the display and you know the value of the column, then include it as a column in the select list

    HTH
    Cheers
    Sathyaram
    Last edited by sathyaram_s; 08-11-04 at 16:41.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58
    sorry, in the above example the LIKE 'p%' (where p is entered as a search criteria)

    So in the above example i'm search against the name column 'peter%' where there could be many name matches per Org_Id, but i only want to display the one name to the user....

    example

    NAME ORD_ID
    peter c 123
    peter b 123

    group by org_id, and display the name (either one doesn't matter since the are in the same org_id)

  4. #4
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58
    Is there any kind of function i can put on just a column to make it distinct?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ApoPen, what you seem to want is not something that is easily produced by sql

    DISTINCT refers to rows

    GROUP BY refers to a set of columns, and any other data in the SELECT list of a grouped query must be aggregate expressions only

    perhaps you could give better examples of what you want?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Not sure I completely understand your question but if you just want to grab
    the first name that happens to have a particular org_id, do as below.

    Basically provide an incremental index within each org_id 'grouping' in a nested select using the OLAP row_number function. Then select the rows that have a row_number value of 1.

    Code:
    SELECT * 
    FROM
    ( 
      SELECT NAME
      ,          ORG_ID
      ,          ROW_NUMBER()OVER(PARTITION BY ORG_ID ORDER BY NAME) RN 
      FROM    YOUR_TABLES
      WHERE NAME LIKE 'p%'
    ) TMP_TBL_WITH_ORG_ID_INDEX
    WHERE RN = 1
    Damian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •