Post reply

multiple joins necessary or redundant for distinct aggregates?

  • January 13, 2009 at 6:42 am

    #127888

    I have a bunch of normalized tables, and I want to get some MAX() dates with different criteria. Do I have to repeatedly join the tables each time with the different criteria? Is there a better way to do this?

    From the same set of tables, for all IDs in the table, I want the max date, the max date when left join table.field=1, and the max date when join othertable.field>4. So, my out put would have four columns, the three dates and an id.

  • January 13, 2009 at 7:41 am

    #925880

    Use CASE. MAX will ignore the NULLs. eg:

    SELECT YourId

    &nbsp&nbsp&nbsp&nbsp,MAX(CASE WHEN othertable.field > 4 THEN YourDate END)

    &nbsp&nbsp&nbsp&nbsp...

  • January 13, 2009 at 7:45 am

    #925884

    You have not provided enough information to help you. If you want a better and a quick solution, help us by providing table structures in form of DDL, sample data in form of INSERTs and the required output.

    For quick/better responses click here[/url]

    --Ramesh

  • January 13, 2009 at 12:31 pm

    #926178

    Yeah, I think that's what I was looking for. I can extrapolate MAX(CASE WHEN [field] IS NOT NULL THEN [date] END) to do what I need.

    Thanks for the guidance

Viewing 4 posts - 1 through 3 (of 3 total)

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