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.
    • 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 2 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 3 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 4 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.