SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    group by numeric values in a field

    I have 3 fields in myTable like the following.

    Code:
     data in myTable
    
    (ID) (name_class_blah_blah) (score)
    (1)  (Jane C3 ogdsbc)        (75) 
    (2)  (kate C2 ihc)           (65) 
    (3)  (Tom C1 kogv)           (85) 
    (4)  (Robert C3 kbgefxcg)    (90) 
    (5)  (Mary C1 bgfds)         (60)
    I like to produce my target output like the following.

    Code:
    target result
    
    (2) (kate C2 ihc)        (65)
    (3) (Tom C1 kogv)        (85)
    (4) (Robert C3 jbgefxcg) (90)

    There are 2 persons in C1. they are Tom and Mary. Tom gets higher score than Mary. So Tom should be produced.

    There are 1 person in C2. She is Kate. No one can get higher score than Kate.
    So Kate should be produced.

    There are 2 persons in C3. they are Jane and Robert.
    Since Robert gets higher score than Jane, Robert should be produced.

    The problem here is that the group key word comes in the middle of the values in "name_class_blah_blah" field.

    Can I produce my target result with your help?


    Thanks in Advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please describe how to find the group key word inside the field

    what does "in the middle" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    please describe how to find the group key word inside the field
    Group Keys are C1, C2, C3.

    Actually they are 1C, 2C, 3C, 4C, 5C...( We say 1 class, 2 class, and 3 class in my mother tongue. But as far as I know, you say class 1, class 2, and class 3 in English)

    From now on I will tell with changing class 1 , class 2, class 2 into 1 class, 2 class, 3 class in my mother tongues's way.


    "name_class_blah_blah" field is a short paragraph.

    For example,

    (1) Jane is in 3 class. She is pretty. She has 2 brothers.
    (2) Kate is in 2 class. She sings and dances well.
    (3) Tom is very tall. He studies in 1 class. He want to be a scientist.
    (4) Robert is in 3 class. He study hard. He knows nothing but study.
    (5) 1 class is Mary's class. the morals of the class is high.

    Every value in "name_class_blah_blah" field surely has the word "class" and a numeric value before the word "class".

    Actually there is surely a space bar between the numeric value and the word "class".

    So the actual group keys are 1 class, 2 class, and 3 class.

    Quote Originally Posted by r937
    what does "in the middle" mean?
    It means the group key comes inside(in the middle) of the paragraph which is the value of the "name_class_blah_blah" field

    Glad for your asking

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    glad for your explaining

    what you should do next is write your own SELECT statement
    Code:
    select ID
         , name_class_blah_blah
         , score
         , expression
      from yourtable
    where expression is some sql function(s) to extract the substring that you want

    you have several problems

    first, the target substring does not appear to be in a fixed place, so you can't use SUBSTRING()

    it has a variable number of spaces in front of it, so you can't use INSTR() to locate the first space

    you can use INSTR to find the actual string, e.g. ' 1C' with a space in front, but that doesn't help, because that's only one of the possible values...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Okay, I'll give this up because it has too many problems as you said.

    However, I'll make my target result simpler. and I'll post it again.

  6. #6
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you really need to do here is break that long field up into separate fields to represent each unique piece of information...
    Code:
    (ID) (name)  (class)   (info)	(score)
    (1)  (Jane)	(C3)   (ogdsbc)	   (75) 
    (2)  (kate)	(C2)   (ihc)	   (65) 
    (3)  (Tom)	(C1)   (kogv)	   (85) 
    (4)  (Robert)   (C3)   (kbgefxcg)  (90) 
    (5)  (Mary)	(C1)   (bgfds)	   (60)
    That way you can query on the 'class' field.

    --Viral
    A computer without Windows is like a chocolate cake without mustard.


Bookmarks

Posting Permissions

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