• 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

    SQL MAX

    I wish I didn't have to write this with SQL. But never mind.

    I have a table:-

    Deployment.

    It contains:-

    batch id (int)
    deployment (datetime)

    e.g.

    1,13/09/2015
    2,14/09/2015
    3,15/09/2015
    4,10/09/2015
    2,11/09/2015
    3,12/09/2015

    Also:-

    Batch.

    Containing

    batch id (int)
    batch type(int)

    I am trying to return the batch id, deployment, type

    But I only want to return the most recent row for each batch type.

    So I am completely stuck; mainly because I know stuff all about SQL. But the join would appear to need some sort of select max(deployment). No idea what though.

    #2
    Something like

    Select b.BatchId, max(d.Deployment), b.BatchType From Deployment d
    Inner join Batch b on b.BatchId=d.BatchId
    Group by b.BatchId,B.BatchType

    ?

    Comment


      #3
      Originally posted by ASB View Post
      I wish I didn't have to write this with SQL. But never mind.

      I have a table:-

      Deployment.

      It contains:-

      batch id (int)
      deployment (datetime)

      e.g.

      1,13/09/2015
      2,14/09/2015
      3,15/09/2015
      4,10/09/2015
      2,11/09/2015
      3,12/09/2015

      Also:-

      Batch.

      Containing

      batch id (int)
      batch type(int)

      I am trying to return the batch id, deployment, type

      But I only want to return the most recent row for each batch type.

      So I am completely stuck; mainly because I know stuff all about SQL. But the join would appear to need some sort of select max(deployment). No idea what though.
      What RDBMS are you on, Oracle, SQL Server, Teradata?
      The greatest trick the devil ever pulled was convincing the world that he didn't exist

      Comment


        #4
        I had tried that (or at least what I believe to be logically the same) but it still returns each individual row.

        Annoyingly of course I could easily just return the entire data set and process that in the consumer but I'm not supposed to do that.

        Comment


          #5
          Originally posted by LondonManc View Post
          What RDBMS are you on, Oracle, SQL Server, Teradata?
          SQL server 2008.

          Comment


            #6
            Originally posted by ASB View Post
            SQL server 2008.
            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
            The greatest trick the devil ever pulled was convincing the world that he didn't exist

            Comment


              #7
              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
              I will give that a go tomorrow and then worry about the union I will need to add all the records with forwards dates.

              Comment


                #8
                Originally posted by ASB View Post
                I will give that a go tomorrow and then worry about the union I will need to add all the records with forwards dates.
                To exclude forward dates, you just need an extra condition

                Add this line before the one that starts "group by...." :
                where a1.deployment < getdate()

                The query includes future dates by default because you didn't specify that there could be future dates
                The greatest trick the devil ever pulled was convincing the world that he didn't exist

                Comment


                  #9
                  Originally posted by LondonManc View Post
                  To exclude forward dates, you just need an extra condition

                  Add this line before the one that starts "group by...." :
                  where a1.deployment < getdate()

                  The query includes future dates by default because you didn't specify that there could be future dates
                  Very true. No I (intentionally) didn't. :-)

                  What I actually need in the final query is (for each batch type), the last one that has actually been deployed and all those that are yet to be deployed.

                  There are in fact various other tables where data is collected from in the real query I am trying to end up with.

                  But I have my start pointer thank you.

                  Comment


                    #10
                    Originally posted by ASB View Post
                    Very true. No I (intentionally) didn't. :-)

                    What I actually need in the final query is (for each batch type), the last one that has actually been deployed and all those that are yet to be deployed.

                    There are in fact various other tables where data is collected from in the real query I am trying to end up with.

                    But I have my start pointer thank you.
                    You could do that as either a union query for the two types of event or use case expressions for each. I'd have to invoice you if I spent any longer on it though
                    The greatest trick the devil ever pulled was convincing the world that he didn't exist

                    Comment

                    Working...
                    X