Group by First Character

how to group by first letter of text

Try something like this:

select
    val
    ,MAX(count) as count
    ,left(val,1) as first_letter
from (
    select
        val
        ,count(val) as count
    from tbl
    group by val
) a
group by left(val, 1);

First get count per val and from this result get the MAXcount grouping by first letter

UPDATE: (thx to Vamsi Prabhala for pointing it out that my first solution wasn't the best one)

After get the count per val, I used a variable to redo the ROW_NUMBER() functionality (from MS-SQL) and select the first row from result, ordered by first_letter and count desc

---

select val, count, first_letter from (
  select
     @i:=CASE
          WHEN @first_letter = first_letter THEN @i + 1
          ELSE 1
     END as rn
     ,@first_letter:= a.first_letter as First_letter
     ,a.val
     ,a.count
  from (
      select
          val
          ,count(val) as count
          ,left(val,1) as first_letter
      from tbl
      group by val
  )a, (select @i:=0) b
  order by First_letter, count desc
  ) c
where rn = 1

How to get the first character of a row and group it with the first character of other rows and create a column from that

You don't need a group by clause, what you are asking is a simple DECODE or CASE expression.

Demo:

with data as
(
select 'ANGELINA' name from dual union all
select 'DAVID' from dual union all
select 'IAN' from dual union all 
select 'NICOLE' from dual union all
select 'ROBIN' from dual

)
-- Your query starts here
select name, 
       case 
         when substr(name, 1, 1) in ('A','B','C')
         then 'Junior'
         when substr(name, 1, 1) in ('D','E','F')
         then 'Senior'
         when substr(name, 1, 1) in ('G','H','I')
         then 'Teacher'
       end as letter 
from data;

NAME     LETTER 
-------- -------
ANGELINA Junior 
DAVID    Senior 
IAN      Teacher
NICOLE          
ROBIN

The with data clause is only to build the sample data as you have not provided any. In your actual query, use your table name instead of data. Remove everything before the comment "-- Your query starts here".

Group array of strings by first letter

I ran your code as well as the test examples provided by JS challenger. I noticed that they where case sensitive. So although your code works well, if the words begin with upper case it will not pass certain cases. Attached is my version that passed all test examples.

If you add : .toLowerCase to the firstChar, I believe you will pass as well. Happy coding ;)

P.S if the image below does not work please let me know, I am just learning how to contribute to Stack Exchange, thanks.

const groupIt = (array) => {
  let resultObj = {};
  
  for (let i =0; i < array.length; i++) {
    let currentWord = array[i];
    let firstChar = currentWord[0].toLowerCase();
    let innerArr = [];
    if (resultObj[firstChar] === undefined) {
       innerArr.push(currentWord);
      resultObj[firstChar] = innerArr
    }else {
      resultObj[firstChar].push(currentWord)
    }
  }
  return resultObj
}

console.log(groupIt(['hola', 'adios', 'chao', 'hemos', 'accion']))

console.log(groupIt(['Alf', 'Alice', 'Ben'])) // { a: ['Alf', 'Alice'], b: ['Ben']}

console.log(groupIt(['Ant', 'Bear', 'Bird'])) // { a: ['Ant'], b: ['Bear', 'Bird']}
console.log(groupIt(['Berlin', 'Paris', 'Prague'])) // { b: ['Berlin'], p: ['Paris', 'Prague']}

Regex replacing the first character in a group

Use this regex,

L(?=\d{4}$)

and replace it with 'A'

This look ahead (?=\d{4}$) makes sure, only L is selected for replacement which is followed by exactly 4 digits and end of string.

Demo

How to group elements effectively in a huge list by their first character in python

You don't really need to use groupby to do this.

Consider your linked example:

list1=['hello','hope','hate','hack','bit','basket','code','come','chess']

You can create the groups described with a native Python dict:

groups={}
for word in list1:
    groups.setdefault(word[0],[]).append(word)

>>> groups
{'h': ['hello', 'hope', 'hate', 'hack'], 'b': ['bit', 'basket'], 'c': ['code', 'come', 'chess']}

Or, with defaultdict if your prefer:

from collections import defaultdict 
groups=defaultdict(list)    
for word in list1:
    groups[word[0]].append(word)

>>> groups
defaultdict(<class 'list'>, {'h': ['hello', 'hope', 'hate', 'hack'], 'b': ['bit', 'basket'], 'c': ['code', 'come', 'chess']})

Both of these methods will work with completely unsorted data and gather the words based on the first letter. You are then free to use the values of that dict to make a list of lists if desired:

>>> sorted(groups.values(), key=lambda s: s[0])
[['bit', 'basket'], ['code', 'come', 'chess'], ['hello', 'hope', 'hate', 'hack']]

Now if you still want to use groupby for some reason, you would likely do something like this:

groups={}
for k,v in groupby(list1, key=lambda s: s[0]):
    groups.setdefault(k,[]).extend(v)

mysql: how to select group by first character and top 5 by counter

You can do:

select *
from (
  select *, row_number() over(partition by substr(person, 1, 1) 
                              order by counter desc) as rn
  from myTable
) x
where rn <= 5
order by substr(person, 1, 1), rn

Result:

 id   person     counter  rn 
 ---- ---------- -------- -- 
 153  Alf        19758    1  
 283  Alycia     19706    2  
 260  Abe        19463    3  
 223  Assunta    18808    4  
 300  Ari        18031    5  
 210  Bennie     18309    1  
 159  Barry      18281    2  
 128  Beulah     18080    3  
 314  Benny      16795    4  
 474  Barry      15789    5  
 342  Casandra   19656    1  
 14   Carson     19537    2  
 67   Chaim      19429    3  
 280  Colin      18507    4  
 500  Corbin     18433    5  
 380  Daphney    19138    1  
 234  Dejah      18781    2  
 241  Derrick    18722    3  
 49   Dasia      18562    4  
 312  Darrel     17903    5  
 163  Evalyn     19847    1  
 79   Ernestine  19523    2  
 344  Emilie     19520    3  
 371  Eva        19119    4  
 469  Emma       18403    5  
 140  Fiona      19522    1  
 216  Flo        18314    2  
 356  Frieda     16082    3  
 254  Floy       15942    4  
 54   Florencio  12739    5  
 447  Geoffrey   19858    1  
 327  Geoffrey   19223    2  
 335  Grant      19100    3  
 454  Giuseppe   16175    4  
 83   Gardner    15235    5  
 373  Hilario    19507    1  
 35   Hanna      19276    2  
 200  Halle      18150    3  
 491  Hailee     17521    4  
 411  Hermann    17018    5  
 21   Idella     7440     1  
 177  Izabella   5536     2  
 115  Isai       4164     3  
 412  Izabella   2112     4  
 275  Imani      573      5  
 195  Joannie    19374    1  
 8    Jacquelyn  19092    2  
 48   Jalon      18861    3  
 251  Jamie      18768    4  
 367  Joanny     17600    5  
 282  Kendra     19278    1  
 421  Kendra     19213    2  
 363  Kaylin     18977    3  
 96   Kaylie     18423    4  
 310  Katrine    17754    5  
 146  Lonzo      19778    1  
 194  Leonora    18258    2  
 399  Laurine    16847    3  
 137  Leslie     16718    4  
 190  Luther     16318    5  
 87   Maegan     19112    1  
 20   Mittie     18928    2  
 271  Mariana    18149    3  
 317  Mary       18043    4  
 305  Maybelle   17666    5  
 281  Noelia     19203    1  
 176  Nickolas   19047    2  
 408  Nelson     15901    3  
 142  Nasir      13700    4  
 366  Nicole     10694    5  
 423  Ova        19759    1  
 487  Osborne    19539    2  
 438  Ozella     18911    3  
 375  Ora        18270    4  
 414  Onie       17358    5  
 52   Pascale    19658    1  
 39   Pearlie    17621    2  
 364  Price      14177    3  
 161  Precious   10337    4  
 294  Paula      9162     5  
 70   Quincy     18343    1  
 73   Quincy     16631    2  
 192  Quentin    13578    3  
 131  Rodger     19776    1  
 231  Royal      19033    2  
 313  Rocky      19008    3  
 13   Rossie     18403    4  
 45   Rosanna    15992    5  
 418  Sydnee     19810    1  
 470  Sadie      19189    2  
 123  Shanna     18862    3  
 485  Savanah    18664    4  
 302  Steve      16412    5  
 406  Toney      18283    1  
 28   Tremaine   16400    2  
 98   Taurean    15911    3  
 278  Tremaine   14391    4  
 311  Treva      14026    5  
 239  Ubaldo     11630    1  
 78   Valentina  17736    1  
 458  Vita       17527    2  
 170  Vergie     16971    3  
 158  Vance      15089    4  
 272  Veronica   12027    5  
 102  Willis     18155    1  
 329  Ward       14919    2  
 156  Westley    14867    3  
 136  Winnifred  14315    4  
 6    Winnifred  13580    5  
 323  Yolanda    17920    1  
 155  Yesenia    6164     2  
 402  Zachary    19129    1  
 37   Zaria      5398     2  

See running example at DB Fiddle.

---

Related Topics