Nick, As you stated, your priority field datatype is "varchar", with possible values "Hi", "Medium" and "Low", as opposed to being integers. The use of max function, as suggested by some colleagues without knowing exactly the datatype would work correctly only on columns of datatype integer. In your case, for textual columns, lexicographic (dictionary) ordering will be used in computing function max, and "Medium" would win the competition, instead of "Hi" -- which actually has the lowest ranking in the lexicographic ordering. Perhaps you could use the " CASE WHEN ..." constructs to map your textual priority into numeric (integer) values (e.g., L -> 1, M -> 2, H -> 3), and then apply the max function to the integer values to get correct results.
Best regards, ________________________ Lin -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 4:41 PM To: Dathan Vance Pattishall Cc: [EMAIL PROTECTED] Subject: RE: SQL Help... I looked at the group by option already and I dont think it will do what I need it to do. I say this because it will only group things in the priority/task/whatever but that still leaves options for duplicate resources. Yes, it would get rid of the dup. resources per priority, but not for the entire table. I really want to group by the resource, but still have all the info available for what ever the highest priority task it is in. -Nick Btw, all the fields are varchars with the priorities being Hi, Medium, Low, but I could really care less on what particular priority it is atm. > -->-----Original Message----- > -->From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > -->Sent: Thursday, July 24, 2003 12:53 PM > -->To: [EMAIL PROTECTED] > -->Subject: SQL Help... > --> > -->After some searching around different books/manuals/google I still > can't > -->seem to figure out how do to this. What I have is a table with 4 cols > -->(task, resource, department, priority) and what I want to do is be > able > -->to > -->select distinct resources and list what their highest priority is. > -->In other words, if a resource is in a high priority task and a low > -->priority task, I only want to show the high priority task. > > Take a look at GROUP BY HAVING and Count(*) at mysql.com > Something like > SELECT resource, task, priority GROUP BY priority, task having priority >> <what you define as task> > > I have no idea what your column types are and what data is contained so > please excuse this guess. > > > > --> > -->Thanks for the help! > -->-Nick > --> > -->-- > -->MySQL General Mailing List > -->For list archives: http://lists.mysql.com/mysql > -->To unsubscribe: > -->http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
