Post reply

SELECT MIN Rank

  • March 15, 2006 at 8:31 am

    #112384

    Hi,

    I'm have the following fields in a view:

    UserID, ProjectID, User_Name, User_Rank

    I am attempting to pull the records that have the lowest user rank for each projectID.  For some reason, I just can't seem to wrap my mind around it.

    Thanks,

    Matt

  • March 15, 2006 at 8:38 am

    #626791

    Wait: I just reread your post

    Is user Rank a value you have in the view?

    Is this what your looking for?

    select UserID, ProjectID, User_Name, min(User_Rank) as User_Rank

    from Myview

    Group by UserID, ProjectID, User_Name

  • March 15, 2006 at 9:14 am

    #626813

    Yes, you're 2nd post is more of what I'm looking for.  However, that still returns more than one user per ProjectID.

  • March 15, 2006 at 9:50 am

    #626832

    Hi All,

    Matt - you're not just looking for this, are you?

    select ProjectID, min(User_Rank) as User_Rank from Myview Group by ProjectID

    If not, could you explain what columns you need, and perhaps even give a small example...

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • March 15, 2006 at 9:53 am

    #626837

    Can we treat it like two tables: one is the whole view, the other is the ProjectID and the lowest rank for that project, so we want to see all the view rows for that project which have that lowest rank?

    If a project has two low-ranking users, *shouldn't* the list return both those rows?

    select p.* from MyView p join (

    select ProjectID, Min(User_Rank) from Myview group by ProjectID)

    ) b on p.ProjectID = b.ProjectID and p.User_Rank = b.User_Rank

  • March 15, 2006 at 9:59 am

    #626839

    Yes, Pat, that was the trick.  Treating it as two tables and referencing itself.  Thanks for your help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply