code to select highest in each of several catagories

  • I have two data tables and a join table and I need to gather information from all three, then limit based on highest value in a SUM field. The tables are

    Employees:

    EmpName

    EmpNum

    Team

    Projects:

    ProgNum

    PRojName

    ProjMan

    EmpProjLog:

    EmpNum

    ProjNum

    Hrs

    LogID

    SELECT Projects.ProjName, (Employees.EmpName) AS EmpName, SUM([Employees Project Log].Hrs) AS TotalHrs

    FROM Employees INNER JOIN

    [Employees Project Log] ON Employees.EmpNum = [Employees Project Log].EmpNum INNER JOIN

    Projects ON [Employees Project Log].ProjNum = Projects.ProjNum

    GROUP BY Projects.ProjName, (Employees.EmpName)

    ORDER BY SUM([Employees Project Log].Hrs) DESC

    Results in This data Set

    Inventory Giles 67

    ClientList Frank 30

    Inventory Frank 20

    SalesData Lynn 10

    ClientList Lynn 5

    I need to limit it so that only the person with the most hrs for each project shows. Clientlist -- Frank, Inventory --Giles, Sales --Lynn.

    Any Idea what I am missing?

    Thanks,

    Linda

  • Take a look at Rank, Dense_Rank, and Row_Number. Those should give you what you need.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • THanks ... ok then this

    SELECT Projects.ProjName, (Employees.EmpName) AS EmpName, SUM([Employees Project Log].Hrs) AS TotalHrs

    , RANK() OVER

    (PARTITION BY Projects.ProjName ORDER BY SUM([Employees Project Log].Hrs) DESC) AS 'MostHrs'

    FROM Employees INNER JOIN

    [Employees Project Log] ON Employees.EmpNum = [Employees Project Log].EmpNum INNER JOIN

    Projects ON [Employees Project Log].ProjNum = Projects.ProjNum

    GROUP BY Projects.ProjName, (Employees.EmpName)

    ORDER BY SUM([Employees Project Log].Hrs) DESC

    tells me which was the top... but when I try to limit with a WHERE or HAVING caluse I get

    Invalid column name 'MostHrs'.

    How can I show only the Rank 1 items?

  • Linda 44925 (11/4/2010)


    THanks ... ok then this

    SELECT Projects.ProjName, (Employees.EmpName) AS EmpName, SUM([Employees Project Log].Hrs) AS TotalHrs

    , RANK() OVER

    (PARTITION BY Projects.ProjName ORDER BY SUM([Employees Project Log].Hrs) DESC) AS 'MostHrs'

    FROM Employees INNER JOIN

    [Employees Project Log] ON Employees.EmpNum = [Employees Project Log].EmpNum INNER JOIN

    Projects ON [Employees Project Log].ProjNum = Projects.ProjNum

    GROUP BY Projects.ProjName, (Employees.EmpName)

    ORDER BY SUM([Employees Project Log].Hrs) DESC

    tells me which was the top... but when I try to limit with a WHERE or HAVING caluse I get

    Invalid column name 'MostHrs'.

    How can I show only the Rank 1 items?

    Use nested select statements. Place another select statement outside of your current statement and use a where clause on your outer select statement and have WHERE MostHrs = 1:

    select *

    from

    (

    SELECT Projects.ProjName, (Employees.EmpName) AS EmpName, SUM([Employees Project Log].Hrs) AS TotalHrs

    , RANK() OVER

    (PARTITION BY Projects.ProjName ORDER BY SUM([Employees Project Log].Hrs) DESC) AS 'MostHrs'

    FROM Employees INNER JOIN

    [Employees Project Log] ON Employees.EmpNum = [Employees Project Log].EmpNum INNER JOIN

    Projects ON [Employees Project Log].ProjNum = Projects.ProjNum

    GROUP BY Projects.ProjName, (Employees.EmpName)

    ORDER BY SUM([Employees Project Log].Hrs) DESC

    )a

    where a.MostHrs = 1

  • That worked perfectly. Thank you both!

  • Mr. Celko, please go away. The question was obviously answered, DDL or not. Why do you feel like you have to stick your "expertise" in where it's not appropriate or appreciated?

    +--------------------------------------------------------------------------------------+
    Check out my blog at https://pianorayk.wordpress.com/

  • CELKO (11/4/2010)


    Columns are nothing like fields.

    Not necessarily true.

    Talk to a business user once in a while (someone who's used Lotus or Excel or Access) and you will find they often interchange the terms columns and fields. A DBA who doesn't understand this and insists on badgering them with the "proper" technobabble will alienate his clients to the detriment of the project and all future projects.

    Sometimes there is such a thing as taking it too far. In this case, there's not enough of a difference to warrant calling someone out over it. Now, if he'd equated a field/column to a record set, that would be entirely different.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Oh, come on Joe!

    This is the newbies forum.

    People that post here didn't read your books (and probably never will, if you treat them this way...)

    -- Gianluca Sartori

  • sheesh. Lighten up celko. lol

    The probability of survival is inversely proportional to the angle of arrival.

  • CELKO (11/4/2010)


    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

    The correct terms are entity and relationship tables. Columns are nothing like fields. Your mindset is still stuck in file systems and Network DBs. Let's try to fix some skeleton DDL for you.

    :w00t: Man do you beat your children with that whip? :hehe:

  • Thank you to all that have defended me from the high handedness. ? However, I think Celko does have a point and I appreciate his intentions if not the methods. I went thought a SQL self taught course with no live instructor, mentor or peers to help me with developing proper terminology and advanced concepts and I know I have tons left to learn.

    Celko,

    Thank you for taking the time to try to educate the masses. In my defense, I do know that the table names should not have spaces, that name was from the original ERD sketch, not what I used in practice. (I’m new but not that raw) The actual relationship table name used in my code was EmpProjLog. And I did not give the CREATE TABLE code because I did not feel it was relevant to the issue and would only make things more difficult to read in this instance.

    I’ve never heard the terms entity and relationship tables but that does make sense and I’m happy to know the correct terminology. I do believe that it is important for everyone to use the same terms to facilitate proper communication. But too, you obviously did know what I meant and as others have pointed out, remember we are newbies here so be gentle. But, Thank you I’ll try to use the proper terms in the future.

    I’ve never done any work with CTE’s before and I would very much appreciate it if you would give some more detailed explanations of why you changed the solution code in the way that you did. I’m also confused by why there is no explicit JOIN (left, right, or any other type) statements anywhere in the code. I can read the code enough to understand it to a point, I see where the joins happen, but why don't you have to yours the JOIN syntax there? . Your solution seems much more convoluted at the least. Is there some future benefit to creating it in this way instead? Would not the fewer lines of code be a more streamlined, efficient approach?

    Thank you,

    Linda

  • Linda 44925 (11/9/2010)


    I’ve never done any work with CTE’s before and I would very much appreciate it if you would give some more detailed explanations of why you changed the solution code in the way that you did. I’m also confused by why there is no explicit JOIN (left, right, or any other type) statements anywhere in the code. I can read the code enough to understand it to a point, I see where the joins happen, but why don't you have to yours the JOIN syntax there? . Your solution seems much more convoluted at the least. Is there some future benefit to creating it in this way instead? Would not the fewer lines of code be a more streamlined, efficient approach?

    Thank you,

    Linda

    You show a lot of maturity by responding to CELKO's post in a civilized manner. You are right, he had some extremely valid points that are key to developing good fundamentals. Unfortunately, I feel his great points were hidden by his condescending approach. Instead of going to your head, he went right over it. I see this a lot with the older well disciplined technicians, but it really discourages and makes learning harder instead of encouraging and making learning easier. It's unfortunate cause I would bet that CELKO has libraries upon libraries of invaluable information in his head - but it is too much and uncomprehendable for beginners. Oh well.

    Anyways, to clarify his syntax...

    CELKO, in his rant, forgot to create his code using ANSI standard syntax. He is using an "old school" form of creating joins.

    The join he used:

    FROM Personnel AS PR,

    Timesheets AS T,

    Projects AS P

    WHERE PR.emp_nbr = T.emp_nbr

    AND T.proj_nbr = P.proj_nbr

    The same join written in syntax that you are probably more familiar with (which your way adheres more to ANSI standards) would be something like:

    FROM Personnel AS PR

    inner join Timesheets AS T

    on PR.emp_nbr = T.emp_nbr

    inner join Projects AS P

    on T.proj_nbr = P.proj_nbr

  • tmitchelar (11/9/2010)


    Linda 44925 (11/9/2010)


    I’ve never done any work with CTE’s before and I would very much appreciate it if you would give some more detailed explanations of why you changed the solution code in the way that you did. I’m also confused by why there is no explicit JOIN (left, right, or any other type) statements anywhere in the code. I can read the code enough to understand it to a point, I see where the joins happen, but why don't you have to yours the JOIN syntax there? . Your solution seems much more convoluted at the least. Is there some future benefit to creating it in this way instead? Would not the fewer lines of code be a more streamlined, efficient approach?

    Thank you,

    Linda

    You show a lot of maturity by responding to CELKO's post in a civilized manner.

    You did indeed. WELL DONE!

    You are right, he had some extremely valid points that are key to developing good fundamentals. Unfortunately, I feel his great points were hidden by his condescending approach.

    Every one of these points are very true. His online persona is what people object to, not the points that he is trying to make.

    CELKO, in his rant, forgot to create his code using ANSI standard syntax. He is using an "old school" form of creating joins.

    Now, this is just too funny. Mr. "Follow all ANSI standards" Celko didn't code his query according to the ANSI standard. Perhaps he needs to throw one of his "follow the ansi standard" rant at himself! :w00t:

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thank you for clerifying the JOIN issue. I do recall studying that the method of creating joins had changed recently but I'd never worked with the old code before. At least now I will recognize it more easily if I ever have to update a system.

    I'm still confused by why the CTE method is better overall? Like I said, it seems harder to follow to me. Is there some performance, or resuability of code advantage to doing it Celko's way rather than the original method?

  • Linda 44925 (11/9/2010)


    Thank you for clerifying the JOIN issue. I do recall studying that the method of creating joins had changed recently but I'd never worked with the old code before. At least now I will recognize it more easily if I ever have to update a system.

    I'm still confused by why the CTE method is better overall? Like I said, it seems harder to follow to me. Is there some performance, or resuability of code advantage to doing it Celko's way rather than the original method?

    I like to think of a CTE as a pre-defined sub-query (well, except for recursive CTEs). It also helps out when testing - you can run just the sub-query to see what it's returning with a lot less effort than a subquery.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 1 through 15 (of 15 total)

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