• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL MAX

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    #11
    Personally I would use a temp table. Or at least heavily comment the sql.

    Far more maintainable.

    Remember to use KISS methodology...

    Comment


      #12
      Originally posted by LondonManc View Post
      Give this a try then:

      select a.batch_id, a.deployment, b.batch_type
      from deployment a join batch b on a.batch_id=b.batch_id
      join
      (select b1.batch_type, max(a1.deployment) maxdeploy
      from deployment a1 join batch b1 on a1.batch_id=b1.batch_id
      group by b1.batch_type) c
      on b.batch_type=c.batch_type and a.deployment=c.maxdeploy
      Including a deployed flag (You may need to change < to <= if today = deployed)

      SQL Fiddle

      Comment


        #13
        Am I missing something? because simply this seems to work fine.

        select d.batch_id, d.deployment, b.batch_type from deployment d
        left join batch b on d.batch_id = b.batch_id
        group by d.batch_id
        order by d.deployment DESC

        oops.. albeit under mysql :-)

        Comment


          #14
          Originally posted by stryker007 View Post
          Am I missing something? because simply this seems to work fine.

          select d.batch_id, d.deployment, b.batch_type from deployment d
          left join batch b on d.batch_id = b.batch_id
          group by d.batch_id
          order by d.deployment DESC

          oops.. albeit under mysql :-)
          Yes, you're missing the bit where the OP only wants the top deployment for each batch type.
          The greatest trick the devil ever pulled was convincing the world that he didn't exist

          Comment


            #15
            That's what my query does as it orders by deployment descending and groups by batch_id.... I can't be sure that's the most efficient query at a glance but it yields the results.

            Comment


              #16
              Originally posted by LondonManc View Post
              You could do that as either a union query for the two types of event or use it as one daase expressions for each. I'd have to invoice you if I spent any longer on it though
              You have been very helpful. I have also been diverted from it so it is tomorrow. I will use a union.

              Comment


                #17
                Thank you for all the help and suggestions.

                Here is what I ended up doing....

                Simple query to return all the data. Then write the c# code to mangle it to what I really want. It was just easier for my skillset that way.

                Comment

                Working...
                X