SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Sep 2008
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complicated query - help needed

    I have inherited this database and can't change the design.

    I need to perform a SELECT query and group certain records together for matching employeeID values.

    The fields:
    employeeID
    field1
    field2
    date

    (1) field1 can be 1,2,3,4 or 5 only

    (2) field2 can be a,b,c or a number from 1-100 (varchar though)

    (3) if field1 and field2 are blank/empty then group together


    So:

    employeeID / field1 / field2 / date

    32 / 3 / / 1297184426
    45 / / b / 1248723499
    32 / 3 / / 1258762988
    20 / / /
    20 / / / 1268722384


    So record 1 and 3 above will be grouped, and so will 4 and 5.

    When I mean grouped, I mean so that only one record will be returned / displayed.

    Also, when it displays only one record due to being 'grouped', I would like it to return the highest 'date' field value (UNIX Timestamp value) (so the newest / latst date) OR if one of the records is empty then show this instead. So in the above example, record 1 & 3 will return a date of 1297184426 and records 4 and 5 will return nothing for date.

  2. #2
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Will you tell us what type of database it is?
    Ryan B | My Blog | Twitter

  3. #3
    SitePoint Addict
    Join Date
    Sep 2008
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL

  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)
    in order for this query to have any chance, you must tell us what "empty" means

    equal to a zero-length string? or actually NULL? or either?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Sep 2008
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    zero-length string

  6. #6
    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)
    that makes it a bit simpler
    Code:
    SELECT employeeID 
         , field1 
         , field2
         , CASE WHEN MIN(date) = ''
                THEN ''
                ELSE MAX(date) END  AS date
      FROM daTable
    GROUP
        BY employeeID 
         , field1 
         , field2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •