Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] Selecting top record from each group

Expand Messages
  • John Andrews
    I need top 1 from each group of job numbers example: JobNo,Date,etc.... 1234, 09/01/2003 1234, 08/01/2003 1234, 02/05/2003 5678, 08/24/2003 5678, 07/01/2003
    Message 1 of 8 , Nov 24, 2003
    • 0 Attachment
      I need top 1 from each group of job numbers
       
      example:
       
      JobNo,Date,etc....
       
      1234, 09/01/2003
      1234, 08/01/2003
      1234, 02/05/2003
      5678, 08/24/2003
      5678, 07/01/2003
      7890, 04/05/2003
      7890, 03/10/2003
       
      I would need to select:
       
      1234, 09/01/2003
      5678, 08/24/2003
      7890, 04/05/2003
       
      If you can help me I would be very thankful.
      ----- Original Message -----
      Sent: Friday, November 21, 2003 11:24 AM
      Subject: Re: [SQLQueriesNoCode] Selecting top record from each group

      Just use

      SELECT TOP 1 FROM ---rest of sql here

      HTH


      Nelson Hall
      Application Architect
      American College of Cardiology
      301-581-3489
      nhall@...

      >>> raymist@... 11/15/03 10:22PM >>>
      I need to select the top record of each jobnumber I do a group by and
      order by and get them to the top of each group but how do I select just
      that first record of each group?



      To unsubscribe from this group, send an email to:
      SQLQueriesNoCode-unsubscribe@yahoogroups.com



      Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
    • John Andrews
      MessageI have duplicate job numbers but I only want the newest record for each job number. ... From: Richard Rogers To: SQLQueriesNoCode@yahoogroups.com Sent:
      Message 2 of 8 , Nov 24, 2003
      • 0 Attachment
        Message
        I have duplicate job numbers but I only want the newest record for each job number.
        ----- Original Message -----
        Sent: Friday, November 21, 2003 11:16 AM
        Subject: RE: [SQLQueriesNoCode] Selecting top record from each group

        If you're grouping your records, and have no duplicate job numbers, how is that different from what you're trying to achieve?
        -----Original Message-----
        From: John Andrews [mailto:raymist@...]
        Sent: Saturday, November 15, 2003 9:22 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] Selecting top record from each group

        I need to select the top record of each jobnumber I do a group by and order by and get them to the top of each group but how do I select just that first record of each group?


        To unsubscribe from this group, send an email to:
        SQLQueriesNoCode-unsubscribe@yahoogroups.com



        Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
        NOTICE: This e-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure, or distribution is prohibited. The contents of this e-mail are confidential. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Please virus check all attachments to prevent widespread contamination and corruption of files and operating systems!


        To unsubscribe from this group, send an email to:
        SQLQueriesNoCode-unsubscribe@yahoogroups.com



        Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
      • Michael Gerholdt
        You d have to do something like Select a.JobNo, [a.Date] from myTable a where [a.Date] = (select max(b.Date) from myTable b where b.JobNo = a.JobNo) This will
        Message 3 of 8 , Nov 25, 2003
        • 0 Attachment
          You'd have to do something like

          Select a.JobNo, [a.Date]
          from myTable a
          where [a.Date] = (select max(b.Date)
          from myTable b
          where b.JobNo = a.JobNo)


          This will work if there is only one entry for each job on a given date. If
          you had

          1234, 09/01/2003
          1234, 09/01/2003
          1234, 08/01/2003
          1234, 02/05/2003

          (Note two instances of 1234, 09/01/2003)

          in the table, you'd get an error since the subquery would return more than
          one result. But perhaps you could fix this by putting "TOP 1" in the
          subquery (if you are using MS SQL Server; you could do similar in Oracle by
          putting "rownum < 2" in the where clause); I don't know, have never tried
          that.

          I'm putting Date in square brackets because Date is a function name/reserved
          word. I always try to use something else, like EntryDate, to avoid use of
          Date for a column name.


          ===========
          John Andrews wrote:
          I need top 1 from each group of job numbers

          example:

          JobNo,Date,etc....

          1234, 09/01/2003
          1234, 08/01/2003
          1234, 02/05/2003
          5678, 08/24/2003
          5678, 07/01/2003
          7890, 04/05/2003
          7890, 03/10/2003

          I would need to select:

          1234, 09/01/2003
          5678, 08/24/2003
          7890, 04/05/2003
        • Richard Rogers
          It seems to me you need to use Max(Date) to get your results. ... From: John Andrews [mailto:raymist@comcast.net] Sent: Monday, November 24, 2003 8:35 PM To:
          Message 4 of 8 , Nov 25, 2003
          • 0 Attachment
            Message
            It seems to me you need to use Max(Date) to get your results. 
            -----Original Message-----
            From: John Andrews [mailto:raymist@...]
            Sent: Monday, November 24, 2003 8:35 PM
            To: SQLQueriesNoCode@yahoogroups.com
            Subject: Re: [SQLQueriesNoCode] Selecting top record from each group

            I need top 1 from each group of job numbers
             
            example:
             
            JobNo,Date,etc....
             
            1234, 09/01/2003
            1234, 08/01/2003
            1234, 02/05/2003
            5678, 08/24/2003
            5678, 07/01/2003
            7890, 04/05/2003
            7890, 03/10/2003
             
            I would need to select:
             
            1234, 09/01/2003
            5678, 08/24/2003
            7890, 04/05/2003
             
            If you can help me I would be very thankful.
            ----- Original Message -----
            Sent: Friday, November 21, 2003 11:24 AM
            Subject: Re: [SQLQueriesNoCode] Selecting top record from each group

            Just use

            SELECT TOP 1 FROM ---rest of sql here

            HTH


            Nelson Hall
            Application Architect
            American College of Cardiology
            301-581-3489
            nhall@...

            >>> raymist@... 11/15/03 10:22PM >>>
            I need to select the top record of each jobnumber I do a group by and
            order by and get them to the top of each group but how do I select just
            that first record of each group?



            To unsubscribe from this group, send an email to:
            SQLQueriesNoCode-unsubscribe@yahoogroups.com



            Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


            To unsubscribe from this group, send an email to:
            SQLQueriesNoCode-unsubscribe@yahoogroups.com



            Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
            NOTICE: This e-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure, or distribution is prohibited. The contents of this e-mail are confidential. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Please virus check all attachments to prevent widespread contamination and corruption of files and operating systems!
          • Todd Lehr
            Select jobno,max(date) from whatever Group by jobNo Todd ... From: John Andrews [mailto:raymist@comcast.net] Sent: Monday, November 24, 2003 6:35 PM To:
            Message 5 of 8 , Nov 25, 2003
            • 0 Attachment

              Select jobno,max(date) from whatever

              Group by jobNo

               

              Todd

              -----Original Message-----
              From: John Andrews [mailto:raymist@...]
              Sent
              :
              Monday, November 24, 2003 6:35 PM
              To: SQLQueriesNoCode@yahoogroups.com
              Subject: Re: [SQLQueriesNoCode] Selecting top record from each group

               

              I need top 1 from each group of job numbers

               

              example:

               

              JobNo,Date,etc....

               

              1234, 09/01/2003

              1234, 08/01/2003

              1234, 02/05/2003

              5678, 08/24/2003

              5678, 07/01/2003

              7890, 04/05/2003

              7890, 03/10/2003

               

              I would need to select:

               

              1234, 09/01/2003

              5678, 08/24/2003

              7890, 04/05/2003

               

              If you can help me I would be very thankful.

              ----- Original Message -----

              Sent: Friday, November 21, 2003 11:24 AM

              Subject: Re: [SQLQueriesNoCode] Selecting top record from each group

               

              Just use

              SELECT TOP 1 FROM ---rest of sql here

              HTH


              Nelson Hall
              Application Architect
              American College of Cardiology
              301-581-3489
              nhall@...

              >>> raymist@... 11/15/03 10:22PM >>>
              I need to select the top record of each jobnumber I do a group by and
              order by and get them to the top of each group but how do I select just
              that first record of each group?



              To unsubscribe from this group, send an email to:
              SQLQueriesNoCode-unsubscribe@yahoogroups.com



              Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



              To unsubscribe from this group, send an email to:
              SQLQueriesNoCode-unsubscribe@yahoogroups.com



              Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
            Your message has been successfully submitted and would be delivered to recipients shortly.