SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query construction question case|coalesce|other

    Hi,

    I want my query to bring back one set of results based on a priority value (a,b,c or d).
    (all other values sought are standard across all records, which means the only differentiator is the priority column).

    Basically bring me back priority 'c' if it is there and if not, bring me back priority 'b'

    I think I can only make priority 'c' come back as NULL, if I use a union all query structure. Should that be what I do or is there a more efficient CASE statement , for example.



    bazz

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess you'll need more of an idea of what the query is like.
    Code MySQL:
    select id
           , name
           , priority
      from table1
    where 1

    so I need to bring back the results with priority 'b', only if there are no records for priority 'c'.
    bazz

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,404
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    I'm not sure I understand your need, but if you want the highest priority, and the only changing value is the priority column, you could use MAX and GROUP BY?
    Code:
    select 
        id
      , name
      , MAX(priority) AS priority
    from table1
    GROUP BY
        id
      , name

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by IBazz View Post
    so I need to bring back the results with priority 'b', only if there are no records for priority 'c'.
    bazz
    what the heck should we do about priority 'd' then?
    r937.com | rudy.ca | 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
  •