View previous topic :: View next topic
|
Author |
Message |
bipinpeter
Active User
Joined: 18 Jun 2007 Posts: 213 Location: Cochin/Kerala/India
|
|
|
|
Hi All,
Please check the below query,
SELECT SUBSTR(A.NAME,1,3) AS NAME
FROM TABLE1 A
GROUP BY NAME;
As per my understating the group by will take input as the output of substring that means group by will take data of 3 bytes.And if there is more than one rows are with same data in input after the group by we will have only one row.
suppose my input as follows,
ANIL
ANITHA
ANISH
ANITA
what will be the output?
As per my understanding i should get only one row with data ANI,but i got 4 rows with ANI in output,why is this?
Bipin |
|
Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
Hi Bipin,
The result is correct as per your query. You need to use the following query for the desired output.
Code: |
SELECT SUBSTR(A.NAME,1,3) AS NAME
FROM TABLE1 A
GROUP BY SUBSTR(A.NAME,1,3); |
|
|
Back to top |
|
|
bipinpeter
Active User
Joined: 18 Jun 2007 Posts: 213 Location: Cochin/Kerala/India
|
|
|
|
Thanks Srihari its working fine... |
|
Back to top |
|
|
bipinpeter
Active User
Joined: 18 Jun 2007 Posts: 213 Location: Cochin/Kerala/India
|
|
|
|
One more doubt if we give SELECT SUBSTR(A.NAME,1,3) AS NAME
ideally the output of SUBSTR will come in NAME field,then why the GROUP BY is not working for NAME???
Bipin |
|
Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
Bipin,
When you group by NAME, all the four names are different
ANIL
ANITHA
ANISH
ANITA
So you are getting all of them in the result. |
|
Back to top |
|
|
|