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

Re: Get bottom or Get Top

Expand Messages
  • Pavel_Alexandrov
    Top row: Select Top 1 name,price,code from mytable order by code Bottom row: Select top 1 name,price,code from mytable order by code DESC this is for MSSQL
    Message 1 of 8 , Nov 3, 2005
      Top row: Select Top 1 name,price,code from mytable order by code

      Bottom row: Select top 1 name,price,code from mytable order by code DESC

      this is for MSSQL

      Pavel
    • alex.benitez@prgx.com
      Yes, these would be the top RECORD POSITION in the group by of Code#. Yet Top is: name price code m&m 1.00
      Message 2 of 8 , Nov 3, 2005

        Yes, these would be the top RECORD POSITION in the group by of Code#.

        Yet Top is:
        name                price                code
        m&m                1.00                222  <--- So this is top what? The first record in the set?
        mars                1.00                333

        tic tac                1.50                444

        Regards,
        Alex Benitez
        I.T. Manager
        PRG Schultz
        732-845-1805



        "John Warner" <john@...>
        Sent by: SQLQueriesNoCode@yahoogroups.com

        11/03/2005 11:56 AM

        Please respond to
        SQLQueriesNoCode@yahoogroups.com

        To
        <SQLQueriesNoCode@yahoogroups.com>
        cc
        Subject
        RE: [SQLQueriesNoCode] Get bottom or Get Top





        I'm not sure I understand what you want for Top? What defines top here? By that I mean:
         
        m&m                1.00                222
        m&m                1.25                222

        Yet Top is:
        m&m                1.00                222  <--- So this is top what? The first record in the set?
        mars                1.00                333

         
        Maybe if you explain the critera for determining Top and Bottom, what (not as the coputer, but as a person looking at your data) is the field that I use to decide top and or bottom?
         
        John Warner
        -----Original Message-----
        From:
        SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of alex.benitez@...
        Sent:
        Thursday, November 03, 2005 8:11 AM
        To:
        SQLQueriesNoCode@yahoogroups.com
        Subject:
        [SQLQueriesNoCode] Get bottom or Get Top


        How do I get the top or bottom of a group by recordset?  e.g.


        name                price                code

        m&m                1.00                222

        m&m                1.25                222

        nestle                1.50                222

        mars                1.00                333

        altoids                1.00                333

        tic tac                1.50                444


        results wanted:


        I want to group by code and get the first(top) record.  Then I want to run another query and get the last(bottom) record.


        e.g.

        Top results:

        name                price                code

        m&m                1.00                222

        mars                1.00                333

        tic tac                 1.50                444


        Bottom results:

        name                price                code

        nestle                1.50                222

        altoids                1.00                333

        tic tac                1.50                444



        Regards,
        Alex Benitez
        I.T. Manager
        PRG Schultz



        YAHOO! GROUPS LINKS




      • John Warner
        Then Pavel s answer should get it for you. I think his syntax will also work in Access in this case if that is your DBMS instead of SQL Server. John Warner ...
        Message 3 of 8 , Nov 3, 2005
          Message
          Then Pavel's answer should get it for you. I think his syntax will also work in Access in this case if that is your DBMS instead of SQL Server.
           
           
          John Warner
          -----Original Message-----
          From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of alex.benitez@...
          Sent: Thursday, November 03, 2005 5:16 PM
          To: SQLQueriesNoCode@yahoogroups.com
          Cc: SQLQueriesNoCode@yahoogroups.com
          Subject: RE: [SQLQueriesNoCode] Get bottom or Get Top


          Yes, these would be the top RECORD POSITION in the group by of Code#.

          Yet Top is:
          name                price                code
          m&m                1.00                222  <--- So this is top what? The first record in the set?
          mars                1.00                333

          tic tac                1.50                444

          Regards,
          Alex Benitez
          I.T. Manager
          PRG Schultz
          732-845-1805



          "John Warner" <john@...>
          Sent by: SQLQueriesNoCode@yahoogroups.com

          11/03/2005 11:56 AM

          Please respond to
          SQLQueriesNoCode@yahoogroups.com

          To
          <SQLQueriesNoCode@yahoogroups.com>
          cc
          Subject
          RE: [SQLQueriesNoCode] Get bottom or Get Top





          I'm not sure I understand what you want for Top? What defines top here? By that I mean:
           
          m&m                1.00                222
          m&m                1.25                222

          Yet Top is:
          m&m                1.00                222  <--- So this is top what? The first record in the set?
          mars                1.00                333

           
          Maybe if you explain the critera for determining Top and Bottom, what (not as the coputer, but as a person looking at your data) is the field that I use to decide top and or bottom?
           
          John Warner
          -----Original Message-----
          From:
          SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of alex.benitez@...
          Sent:
          Thursday, November 03, 2005 8:11 AM
          To:
          SQLQueriesNoCode@yahoogroups.com
          Subject:
          [SQLQueriesNoCode] Get bottom or Get Top


          How do I get the top or bottom of a group by recordset?  e.g.


          name                price                code

          m&m                1.00                222

          m&m                1.25                222

          nestle                1.50                222

          mars                1.00                333

          altoids                1.00                333

          tic tac                1.50                444


          results wanted:


          I want to group by code and get the first(top) record.  Then I want to run another query and get the last(bottom) record.


          e.g.

          Top results:

          name                price                code

          m&m                1.00                222

          mars                1.00                333

          tic tac                 1.50                444


          Bottom results:

          name                price                code

          nestle                1.50                222

          altoids                1.00                333

          tic tac                1.50                444



          Regards,
          Alex Benitez
          I.T. Manager
          PRG Schultz



          YAHOO! GROUPS LINKS




        • Damhuis Anton
          TOP 10 Select top 10 * from order by ASC BOTTOM 10Select top 10 * from order by DESCHowever if you do not
          Message 4 of 8 , Nov 3, 2005
            TOP 10
            Select top 10 * from <table> order by <order column> ASC
             
            BOTTOM 10
            Select top 10 * from <table> order by <order column> DESC

            However if you do not have a specific column to order by then you can do the following:
             
            Have a look at:
             
            This is what I got so far, but is not working correctly. Hopefully it canget you started.
             
            Regards
              Anton
             
            Set Nocount ON
            Create Table #Stock (name varchar(10) , price decimal(15,2) , code int)
             
            Insert into #Stock (name , price , code ) values ('m&m' , 1.00 , 222)
            Insert into #Stock (name , price , code ) values ('m&m' , 1.25 , 222)
            Insert into #Stock (name , price , code ) values ('nestle' , 1.50 , 222)
            Insert into #Stock (name , price , code ) values ('mars' , 1.00 , 333)
            Insert into #Stock (name , price , code ) values ('altoids' , 1.00 , 333)
            Insert into #Stock (name , price , code ) values ('tic tac' , 1.50 , 444)
             
            Select count(*) as ID ,
                   s1.name ,
                   s1.price ,
                   s1.code
              from #Stock s1, #Stock s2
               where s1.name + cast(s1.price as varchar(10)) + cast(s1.code as varchar(10)) >= s2.name + Cast(s2.price as varchar(10)) + Cast(s2.code as varchar(10))
               group by s1.name , s1.price , s1.code
               order by 1
             
            Drop table #Stock
            -----Original Message-----
            From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com]On Behalf Of alex.benitez@...
            Sent: 03 November 2005 03:11
            To: SQLQueriesNoCode@yahoogroups.com
            Subject: [SQLQueriesNoCode] Get bottom or Get Top


            How do I get the top or bottom of a group by recordset?  e.g.

            name                price                code
            m&m                1.00                222
            m&m                1.25                222
            nestle                1.50                222
            mars                1.00                333
            altoids                1.00                333
            tic tac                1.50                444

            results wanted:

            I want to group by code and get the first(top) record.  Then I want to run another query and get the last(bottom) record.

            e.g.
            Top results:
            name                price                code
            m&m                1.00                222
            mars                1.00                333
            tic tac                 1.50                444

            Bottom results:
            name                price                code
            nestle                1.50                222
            altoids                1.00                333
            tic tac                1.50                444


            Regards,
            Alex Benitez
            I.T. Manager
            PRG Schultz

            Confidentiality Warning
            =======================
            
            The contents of this e-mail and any accompanying documentation
            are confidential and any use thereof, in what ever form, by anyone
            other than the addressee is strictly prohibited.
            
          • alex.benitez@prgx.com
            I didn t get Pavel s answer..can you forward to me please? Regards, Alex Benitez I.T. Manager PRG Schultz John Warner Sent by:
            Message 5 of 8 , Nov 4, 2005

              I didn't get Pavel's answer..can you forward to me please?

              Regards,
              Alex Benitez
              I.T. Manager
              PRG Schultz




              "John Warner" <john@...>
              Sent by: SQLQueriesNoCode@yahoogroups.com

              11/03/2005 05:57 PM

              Please respond to
              SQLQueriesNoCode@yahoogroups.com

              To
              <SQLQueriesNoCode@yahoogroups.com>
              cc
              Subject
              RE: [SQLQueriesNoCode] Get bottom or Get Top





              Then Pavel's answer should get it for you. I think his syntax will also work in Access in this case if that is your DBMS instead of SQL Server.
               
               
              John Warner
              -----Original Message-----
              From:
              SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of alex.benitez@...
              Sent:
              Thursday, November 03, 2005 5:16 PM
              To:
              SQLQueriesNoCode@yahoogroups.com
              Cc:
              SQLQueriesNoCode@yahoogroups.com
              Subject:
              RE: [SQLQueriesNoCode] Get bottom or Get Top


              Yes, these would be the top RECORD POSITION in the group by of Code#.


              Yet Top is:

              name                price                code

              m&m                1.00                222  <--- So this is top what? The first record in the set?

              mars                1.00                333

              tic tac                1.50                444


              Regards,
              Alex Benitez
              I.T. Manager
              PRG Schultz
              732-845-1805



              "John Warner" <john@...>
              Sent by: SQLQueriesNoCode@yahoogroups.com

              11/03/2005 11:56 AM

              Please respond to
              SQLQueriesNoCode@yahoogroups.com


              To
              <SQLQueriesNoCode@yahoogroups.com>
              cc
              Subject
              RE: [SQLQueriesNoCode] Get bottom or Get Top







              I'm not sure I understand what you want for Top? What defines top here? By that I mean:

               

              m&m                1.00                222

              m&m                1.25                222

              Yet Top is:

              m&m                1.00                222  <--- So this is top what? The first record in the set?

              mars                1.00                333

               
              Maybe if you explain the critera for determining Top and Bottom, what (not as the coputer, but as a person looking at your data) is the field that I use to decide top and or bottom?

               

              John Warner

              -----Original Message-----
              From:
              SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of alex.benitez@...
              Sent:
              Thursday, November 03, 2005 8:11 AM
              To:
              SQLQueriesNoCode@yahoogroups.com
              Subject:
              [SQLQueriesNoCode] Get bottom or Get Top



              How do I get the top or bottom of a group by recordset?  e.g.


              name                price                code

              m&m                1.00                222

              m&m                1.25                222

              nestle                1.50                222

              mars                1.00                333

              altoids                1.00                333

              tic tac                1.50                444


              results wanted:


              I want to group by code and get the first(top) record.  Then I want to run another query and get the last(bottom) record.


              e.g.

              Top results:

              name                price                code

              m&m                1.00                222

              mars                1.00                333

              tic tac                 1.50                444


              Bottom results:

              name                price                code

              nestle                1.50                222

              altoids                1.00                333

              tic tac                1.50                444



              Regards,
              Alex Benitez
              I.T. Manager
              PRG Schultz




              YAHOO! GROUPS LINKS



              YAHOO! GROUPS LINKS




            Your message has been successfully submitted and would be delivered to recipients shortly.