GROUP BY question

ApoPen · #1 (**permalink**) 08-11-04, 14:53

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

sathyaram_s · #2 (**permalink**) 08-11-04, 15:30

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

ApoPen · #3 (**permalink**) 08-11-04, 16:06

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)

ApoPen · #4 (**permalink**) 08-11-04, 16:11

Is there any kind of function i can put on just a column to make it distinct?

r937 · #5 (**permalink**) 08-11-04, 18:42

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?

Damian Ibbotson · #6 (**permalink**) 08-12-04, 04:57

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

Internet Services

Web Development

Digital Marketing

Consumer Tech