Home » SQL & PL/SQL » SQL & PL/SQL » query to find the number of alphabets (TOAD)
query to find the number of alphabets [message #582618] Sat, 20 April 2013 03:34 Go to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
Hello

I need to find out that
assume i have a table having 2 column

Num Name
1 Adam
2 Akanksha
1 barren
2 bosli
3 Benergee
4 Bhawna
3 anjani



I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4
and again there is anjani so 3 should be there as 1 and 2 in first 2 places
and the num should be automatically generated number based on the count of the alphabets

Please help me to make that query


Thanks

[Updated on: Sat, 20 April 2013 03:42]

Report message to a moderator

Re: query to find the number of alphabets [message #582620 is a reply to message #582618] Sat, 20 April 2013 04:47 Go to previous messageGo to next message
sss111ind
Messages: 473
Registered: April 2012
Location: India
Senior Member

I think you are looking for this.
          
WITH datum 
     AS (SELECT 'Adam' NAME FROM dual UNION ALL
         SELECT 'Akanksha' NAME FROM dual UNION ALL
         SELECT 'barren' NAME FROM dual UNION ALL
         SELECT 'anjani' NAME FROM dual UNION ALL
         SELECT 'bosli' NAME FROM dual UNION ALL
         SELECT 'Bhawna' NAME FROM dual UNION ALL
         SELECT 'Benergee' NAME FROM dual UNION ALL
         SELECT 'Smith' NAME FROM dual UNION ALL
         SELECT 'king' NAME FROM dual UNION ALL
         SELECT 'scott' NAME FROM dual UNION ALL
         SELECT 'Miller' NAME FROM dual )
SELECT name, 
       Row_number() 
         over ( 
           PARTITION BY first_letter 
           ORDER BY rn) num 
FROM   (SELECT name, 
               ROWNUM  rn, 
               Substr(Upper(NAME), 1, 1) first_letter 
        FROM   datum)
ORDER BY rn;  
Re: query to find the number of alphabets [message #582622 is a reply to message #582618] Sat, 20 April 2013 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58905
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:

Michel Cadot wrote on Thu, 11 April 2013 12:14

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.
...
Unclear, show us what you see and what you want.


And FEEDBACK to those that answer and help you.

Regards
Michel

Re: query to find the number of alphabets [message #582624 is a reply to message #582620] Sat, 20 April 2013 05:38 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
Thanks
sss111ind


you are awesome..you have resolved my issue...


Smile Smile Smile Smile Smile Cool Cool Cool Cool Cool

Re: query to find the number of alphabets [message #582626 is a reply to message #582624] Sat, 20 April 2013 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58905
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You mean he did your homework?

Regards
Michel
Re: query to find the number of alphabets [message #582628 is a reply to message #582626] Sat, 20 April 2013 05:59 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
hah......Yes he did it.It was really very helpful...

[Updated on: Sat, 20 April 2013 05:59]

Report message to a moderator

Re: query to find the number of alphabets [message #582629 is a reply to message #582628] Sat, 20 April 2013 06:35 Go to previous message
Michel Cadot
Messages: 58905
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And did you learn something?
I mean are you able to use what he posted? are you able to find by yourself the solution for a similar question?
In the end, do you REALLY understand the solution?

Regards
Michel
Previous Topic: how to show total number of department
Next Topic: String sorting
Goto Forum:
  


Current Time: Wed Aug 27 09:58:13 CDT 2014

Total time taken to generate the page: 0.06869 seconds