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

RE: [SQLQueriesNoCode] Re: Subqueries using Values in the Current Row

Expand Messages
  • Damhuis Anton
    Hi My 2c s.... Personally I thing a field name like accno is one extreme, and on the other end strAccountNumberClient is the other extreme. Same as table
    Message 1 of 26 , Aug 16, 2005
      Hi

      My 2c's....

      Personally I thing a field name like "accno" is one extreme, and on the other end strAccountNumberClient is the other extreme.

      Same as table name of "clnt" versus "tbl_Client" being the other extreme.

      I have also seen things like field name "PKClientID" and in other tables things like "FKClientID". This is also an overkill in my books, and would think all "class room" examples thought to students, to show the concept.

      However field names should be distinct enough to convey what it is used for.

      When working in VB and ASP I do use "str" and "lng" as prefixes, as in that case it conveys more info.


      Regards
      Anton


      -----Original Message-----
      From: SQLQueriesNoCode@yahoogroups.com
      [mailto:SQLQueriesNoCode@yahoogroups.com]On Behalf Of John Warner
      Sent: 16 August 2005 04:02
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: RE: [SQLQueriesNoCode] Re: Subqueries using Values in the
      Current Row


      Ah unnecessarily verbose, the words of someone who has never had to deal
      with fixing another developers code.

      John Warner

      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.
    • Thompson, Mark T. (IS)
      I respectfully disagree. strAccountNumberClient may sometimes not be enough in my book. You would have to get really crazy to be too extreme. Most developers I
      Message 2 of 26 , Aug 16, 2005
        I respectfully disagree. strAccountNumberClient may sometimes not be enough in my book. You would have to get really crazy to be too extreme. Most developers I have seen who use such short names are loners. They do not have to work with other developers. THEY know what the names are, and that is all that matters to them. They will never work under my supervision, because they will never have been hired in the first place if I am one of the people in the interview team. (..and I always am...)


        -----Original Message-----
        From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Damhuis Anton
        Sent: Wednesday, August 17, 2005 12:12 AM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: RE: [SQLQueriesNoCode] Re: Subqueries using Values in the Current Row


        Hi


        My 2c's....

        Personally I thing a field name like "accno" is one extreme, and on the other end strAccountNumberClient is the other extreme.

        Same as table name of "clnt" versus "tbl_Client" being the other extreme.

        I have also seen things like field name "PKClientID" and in other tables things like "FKClientID". This is also an overkill in my books, and would think all "class room" examples thought to students, to show the concept.

        However field names should be distinct enough to convey what it is used for.

        When working in VB and ASP I do use "str" and "lng" as prefixes, as in that case it conveys more info.


        Regards
        Anton


        -----Original Message-----
        From: SQLQueriesNoCode@yahoogroups.com
        [mailto:SQLQueriesNoCode@yahoogroups.com]On Behalf Of John Warner
        Sent: 16 August 2005 04:02
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: RE: [SQLQueriesNoCode] Re: Subqueries using Values in the
        Current Row


        Ah unnecessarily verbose, the words of someone who has never had to deal
        with fixing another developers code.

        John Warner

        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.




        Yahoo! Groups Links
      • vijayasekar shanmughasundharraj
        ... If I wish to group by [or order by] sum(amt) how can I do that? Also, is it possible to select more than one colum in the subquery and how to refer
        Message 3 of 26 , Aug 17, 2005
          Quoting  vfission@...:
          
          
          > Hi Vijay
          If I wish to group by [or
          order by] sum(amt) how can I do
          that? Also, is it possible to select more than one colum in the
          subquery and how to refer to them? Thank you for your kind
          reply. --- Anandavalli --- vjsekars@... wrote:
          > Hi
          > > U can try the below > > Table design: > > tbl name: aa >
          Fields:invno,amnt,ddate
          > > Query: > > select invno,(select
          sum(amnt) from aa where
          > invno='' and ddate='') as
          latestpay,(select
          > sum(amnt) from aa where invno='' and ddate<'')
          as
          > oldpay from aa where invno='' > > bye > with rgds >
          vijaysekar.s
          > > Quoting xdor@...: > > > Is it possible to
          create a subquery that will
          > select from the same
          > table, but exclusive of the record that currently is > being
          retrieved?
          > > Example: > > I am trying to select the sum of
          all
          > previous
          payments that have been
          > made on a paticular invoice previous to the
          one I
          > am currently > retrieving in the SQL statement: > > SELECT
          amount
          As AmountOfThisPayment, (SELECT
          > SUM(amount) FROM > payments WHERE
          payments.invoice_id =
          > payments.invoice_id AND > payments.paydate <
          payments.paydate) As
          > SumOfOtherPayments > FROM payments > > The
          "payments.paydate < payments.paydate" is an
          > obvious logic loop >
          that I
          supposed the SQL engine would understand
          > by context > (since it
          understands "invoice_id = invoice_id").
          > But the statement > only
          returns NULL, even if there are values that
          > fit the criteria. > Of
          course there may be some special way to say
          > it. > > Such as: >
          > "AND
          payments.paydate < THIS_ROW.paydate"
          > > I don't think such a SQL
          function exists, even if
          > it is called > something else. What am I
          missing here?
          > > Please advise. > > > > > > > >
          Yahoo! Groups Links
          > > > > > > > >
          ------------------------ Yahoo! Groups Sponsor
          --------------------~-->
          href="http://us.ard.yahoo.com/SIG=12hmr2veh/M=362335.6886445.7839731.151
          0227/D=groups/S=1705115366:TM/Y=YAHOO/EXP=1124075279/A=2894361/R=0/SIG=1
          3jmebhbo/*http://www.networkforgood.org/topics/education/digitaldivide/?
          source=YAHOO&cmpgn=GRP&RTP=http://groups.yahoo.com/">In low income
          neighborhoods, 84% do not own computers. At Network for Good, help
          bridge the Digital
          Divide!.
          ---------------------------------------------------
          -----------------~-> Yahoo! Groups Links <*> To visit your
          group on the web, go to:
          http://groups.yahoo.com/group/SQLQueriesNoCode/ <*> To unsubscribe
          from this group, send an email to:
          SQLQueriesNoCode-unsubscribe@yahoogroups.com <*> Your use of Yahoo!
          Groups is subject to: http://docs.yahoo.com/info/terms/
        • Marcel Buijs
          Dear @, Is it possible to pick a random record out of a already made selection? for example: I ve got a music-record database. I want to pick up a random
          Message 4 of 26 , Sep 11, 2005
            Dear @,

            Is it possible to pick a random record out of a already made selection?

            for example:

            I've got a music-record database.
            I want to pick up a random record out of an already made selection: Artist.

            the first part is easy:
            select * from music_database where artist = ''

            many thanks in advance
            Marcel.
          • John Warner
            Since you say nothing about the DBMS I will assume SQL Server, place the first result set in a temp table with a numbered key (I would use and Integer, not
            Message 5 of 26 , Sep 11, 2005
              Since you say nothing about the DBMS I will assume SQL Server, place the
              first result set in a temp table with a numbered key (I would use and
              Integer, not guid) and then use the Rand() function (see books online,
              creates a number between 0 and 1) and query the temp table, once you have a
              result drop the temp table.

              John Warner




              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Marcel Buijs
              > Sent: Sunday, September 11, 2005 3:13 AM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: [SQLQueriesNoCode] A random selection out of a selection.
              >
              >
              > Dear @,
              >
              > Is it possible to pick a random record out of a already made
              > selection?
              >
              > for example:
              >
              > I've got a music-record database.
              > I want to pick up a random record out of an already made
              > selection: Artist.
              >
              > the first part is easy:
              > select * from music_database where artist = ''
              >
              > many thanks in advance
              > Marcel.
              >
              >
              >
              >
              >
              > ------------------------ Yahoo! Groups Sponsor
              > --------------------~-->
              > Fair play? Video games influencing politics. Click and talk
              > back! http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/m7folB/TM
              > --------------------------------------------------------------
              > ------~->
              >
              >
              > Yahoo! Groups Links
              >
              >
              >
              >
              >
              >
              >
              >
            • Damhuis Anton
              Hi MarcelYou can use the NEWID() command and then sort ASC or DESC Something like: Select Top 1 * , newID() as RandomID from where artists =
              Message 6 of 26 , Sep 11, 2005
                Hi Marcel

                You can use the NEWID() command and then sort ASC or DESC
                Something like:
                Select Top 1 * , newID() as RandomID
                from <table>
                where artists = <something>
                order by RandomID DESC


                Regards
                Anton

                -----Original Message-----
                From: SQLQueriesNoCode@yahoogroups.com
                [mailto:SQLQueriesNoCode@yahoogroups.com]On Behalf Of Marcel Buijs
                Sent: 11 September 2005 09:13
                To: SQLQueriesNoCode@yahoogroups.com
                Subject: [SQLQueriesNoCode] A random selection out of a selection.


                Dear @,

                Is it possible to pick a random record out of a already made selection?

                for example:

                I've got a music-record database.
                I want to pick up a random record out of an already made selection: Artist.

                the first part is easy:
                select * from music_database where artist = ''

                many thanks in advance
                Marcel.





                ------------------------ Yahoo! Groups Sponsor --------------------~-->
                Fair play? Video games influencing politics. Click and talk back!
                http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/m7folB/TM
                --------------------------------------------------------------------~->


                Yahoo! Groups Links








                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.
              • prasanna Raavi
                hai, I want help from u, I am giving table that has 3 fields Empid, deptid, Doj(dateofjoining) now i want Dept wise first joined employeeid Regs, prasu ...
                Message 7 of 26 , Sep 12, 2005
                  hai,
                   
                  I want help from u,
                   
                  I am giving table  that has 3 fields Empid, deptid, Doj(dateofjoining)
                   
                  now i want Dept wise first joined employeeid
                   
                   
                  Regs,
                  prasu


                  Yahoo! India Matrimony: Find your partner now.
                • John Warner
                  Welcome to Relational Database 101 and your first homework assignment this semester. Your textbook should cover basic selects, but here we go, first
                  Message 8 of 26 , Sep 12, 2005
                    Message
                    Welcome to 'Relational Database 101'  and your first homework assignment this semester. Your textbook should cover basic selects, but here we go, first assignment handed to you by the group:
                     
                    SELECT deptid, Doj AS dateofjoining, Empid
                    FROM TableName;
                     
                    You don't say which DBMS, but this is pretty generic and should work with all the 'biggies'.
                     
                    Basically you select the fields in the order you want them displayed. I am giving you a field alias for Doj as it sort of looks like the parens are an attempt to do that.
                     
                    Hope this helps.
                     
                    Also what is your major?

                    John Warner

                    -----Original Message-----
                    From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of prasanna Raavi
                    Sent: Monday, September 12, 2005 1:09 PM
                    To: SQLQueriesNoCode@yahoogroups.com
                    Subject: RE: [SQLQueriesNoCode] A random selection out of a selection.

                    hai,
                     
                    I want help from u,
                     
                    I am giving table  that has 3 fields Empid, deptid, Doj(dateofjoining)
                     
                    now i want Dept wise first joined employeeid
                     
                     
                    Regs,
                    prasu


                    Yahoo! India Matrimony: Find your partner now.

                  • Ronda
                    now i want Dept wise first joined employeeid If you wanted them ordered by date joined ... then modify Johns query to SELECT deptid, Doj AS dateofjoining,
                    Message 9 of 26 , Sep 12, 2005
                      Message
                      "now i want Dept wise first joined employeeid"
                      If you wanted them ordered by date joined ...  then modify Johns query to
                       
                      SELECT deptid, Doj AS dateofjoining, Empid
                      FROM TableName
                      order by Doj;
                       
                       
                       
                       
                      Ronda
                      (secretly misses the problems with homework for the nightmare of reality)
                       
                       


                      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                      Sent: Monday, September 12, 2005 12:52 PM
                      To: SQLQueriesNoCode@yahoogroups.com
                      Subject: RE: [SQLQueriesNoCode] A random selection out of a selection.

                      Welcome to 'Relational Database 101'  and your first homework assignment this semester. Your textbook should cover basic selects, but here we go, first assignment handed to you by the group:
                       
                      SELECT deptid, Doj AS dateofjoining, Empid
                      FROM TableName;
                       
                      You don't say which DBMS, but this is pretty generic and should work with all the 'biggies'.
                       
                      Basically you select the fields in the order you want them displayed. I am giving you a field alias for Doj as it sort of looks like the parens are an attempt to do that.
                       
                      Hope this helps.
                       
                      Also what is your major?

                      John Warner

                      -----Original Message-----
                      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of prasanna Raavi
                      Sent: Monday, September 12, 2005 1:09 PM
                      To: SQLQueriesNoCode@yahoogroups.com
                      Subject: RE: [SQLQueriesNoCode] A random selection out of a selection.

                      hai,
                       
                      I want help from u,
                       
                      I am giving table  that has 3 fields Empid, deptid, Doj(dateofjoining)
                       
                      now i want Dept wise first joined employeeid
                       
                       
                      Regs,
                      prasu


                      Yahoo! India Matrimony: Find your partner now.

                    • Marcel Buijs
                      Dear John, I thougth that the list was SQLQueriesNoCode.. I thougth about this option, but it only works when I am the only user. I d make a temp database
                      Message 10 of 26 , Sep 12, 2005
                        Dear John,

                        I thougth that the list was SQLQueriesNoCode..

                        I thougth about this option, but it only works when I am the only user.
                        I'd make a temp database witch names the same as the session.

                        That's why I asked this list, becouse I thought there was an more easy way
                        to do it.

                        MArcel.

                        -----Oorspronkelijk bericht-----
                        Van: SQLQueriesNoCode@yahoogroups.com
                        [mailto:SQLQueriesNoCode@yahoogroups.com]Namens John Warner
                        Verzonden: zondag 11 september 2005 17:58
                        Aan: SQLQueriesNoCode@yahoogroups.com
                        Onderwerp: RE: [SQLQueriesNoCode] A random selection out of a selection.


                        Since you say nothing about the DBMS I will assume SQL Server, place the
                        first result set in a temp table with a numbered key (I would use and
                        Integer, not guid) and then use the Rand() function (see books online,
                        creates a number between 0 and 1) and query the temp table, once you have a
                        result drop the temp table.

                        John Warner




                        > -----Original Message-----
                        > From: SQLQueriesNoCode@yahoogroups.com
                        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Marcel Buijs
                        > Sent: Sunday, September 11, 2005 3:13 AM
                        > To: SQLQueriesNoCode@yahoogroups.com
                        > Subject: [SQLQueriesNoCode] A random selection out of a selection.
                        >
                        >
                        > Dear @,
                        >
                        > Is it possible to pick a random record out of a already made
                        > selection?
                        >
                        > for example:
                        >
                        > I've got a music-record database.
                        > I want to pick up a random record out of an already made
                        > selection: Artist.
                        >
                        > the first part is easy:
                        > select * from music_database where artist = ''
                        >
                        > many thanks in advance
                        > Marcel.
                        >
                        >
                        >
                        >
                        >
                        > ------------------------ Yahoo! Groups Sponsor
                        > --------------------~-->
                        > Fair play? Video games influencing politics. Click and talk
                        > back! http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/m7folB/TM
                        > --------------------------------------------------------------
                        > ------~->
                        >
                        >
                        > Yahoo! Groups Links
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >






                        Yahoo! Groups Links
                      • Marcel Buijs
                        I ll try this one, thanks marcel. ... Van: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com]Namens Damhuis Anton Verzonden: maandag 12
                        Message 11 of 26 , Sep 12, 2005
                          I'll try this one,

                          thanks marcel.

                          -----Oorspronkelijk bericht-----
                          Van: SQLQueriesNoCode@yahoogroups.com
                          [mailto:SQLQueriesNoCode@yahoogroups.com]Namens Damhuis Anton
                          Verzonden: maandag 12 september 2005 7:13
                          Aan: SQLQueriesNoCode@yahoogroups.com
                          Onderwerp: RE: [SQLQueriesNoCode] A random selection out of a selection.



                          Hi Marcel

                          You can use the NEWID() command and then sort ASC or DESC
                          Something like:
                          Select Top 1 * , newID() as RandomID
                          from <table>
                          where artists = <something>
                          order by RandomID DESC


                          Regards
                          Anton

                          -----Original Message-----
                          From: SQLQueriesNoCode@yahoogroups.com
                          [mailto:SQLQueriesNoCode@yahoogroups.com]On Behalf Of Marcel Buijs
                          Sent: 11 September 2005 09:13
                          To: SQLQueriesNoCode@yahoogroups.com
                          Subject: [SQLQueriesNoCode] A random selection out of a selection.


                          Dear @,

                          Is it possible to pick a random record out of a already made selection?

                          for example:

                          I've got a music-record database.
                          I want to pick up a random record out of an already made selection: Artist.

                          the first part is easy:
                          select * from music_database where artist = ''

                          many thanks in advance
                          Marcel.







                          Yahoo! Groups Links








                          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.




                          Yahoo! Groups Links
                        • John Warner
                          When I took relational database (Ellison still worked at IBM) we didn t do ORDER BY clauses for a couple of lessons after we were SELECTing, never dawned on me
                          Message 12 of 26 , Sep 12, 2005
                            Message
                            When I took relational database (Ellison still worked at IBM) we didn't do ORDER BY clauses for a couple of lessons after we were SELECTing, never dawned on me to sort this assignment. Good thing you were looking over my shoulder <wink>
                             
                             

                            John Warner

                            -----Original Message-----
                            From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ronda
                            Sent: Monday, September 12, 2005 2:06 PM
                            To: SQLQueriesNoCode@yahoogroups.com
                            Subject: RE: [SQLQueriesNoCode] A random selection out of a selection.

                            "now i want Dept wise first joined employeeid"
                            If you wanted them ordered by date joined ...  then modify Johns query to
                             
                            SELECT deptid, Doj AS dateofjoining, Empid
                            FROM TableName
                            order by Doj;
                             
                             
                             
                             
                            Ronda
                            (secretly misses the problems with homework for the nightmare of reality)
                             
                             


                            From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                            Sent: Monday, September 12, 2005 12:52 PM
                            To: SQLQueriesNoCode@yahoogroups.com
                            Subject: RE: [SQLQueriesNoCode] A random selection out of a selection.

                            Welcome to 'Relational Database 101'  and your first homework assignment this semester. Your textbook should cover basic selects, but here we go, first assignment handed to you by the group:
                             
                            SELECT deptid, Doj AS dateofjoining, Empid
                            FROM TableName;
                             
                            You don't say which DBMS, but this is pretty generic and should work with all the 'biggies'.
                             
                            Basically you select the fields in the order you want them displayed. I am giving you a field alias for Doj as it sort of looks like the parens are an attempt to do that.
                             
                            Hope this helps.
                             
                            Also what is your major?

                            John Warner

                            -----Original Message-----
                            From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of prasanna Raavi
                            Sent: Monday, September 12, 2005 1:09 PM
                            To: SQLQueriesNoCode@yahoogroups.com
                            Subject: RE: [SQLQueriesNoCode] A random selection out of a selection.

                            hai,
                             
                            I want help from u,
                             
                            I am giving table  that has 3 fields Empid, deptid, Doj(dateofjoining)
                             
                            now i want Dept wise first joined employeeid
                             
                             
                            Regs,
                            prasu


                            Yahoo! India Matrimony: Find your partner now.

                          • John Warner
                            Well I gave you functions and data types in TSQL (SQL Server s SQL variant). As I said you didn t mention the DBMS, so I took the easiest route John
                            Message 13 of 26 , Sep 12, 2005
                              Well I gave you functions and data types in TSQL (SQL Server's SQL
                              variant). As I said you didn't mention the DBMS, so I took the easiest
                              route <grin>

                              John Warner




                              > -----Original Message-----
                              > From: SQLQueriesNoCode@yahoogroups.com
                              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Marcel Buijs
                              > Sent: Monday, September 12, 2005 3:24 PM
                              > To: SQLQueriesNoCode@yahoogroups.com
                              > Subject: RE: [SQLQueriesNoCode] A random selection out of a selection.
                              >
                              >
                              > Dear John,
                              >
                              > I thougth that the list was SQLQueriesNoCode..
                              >
                              > I thougth about this option, but it only works when I am the
                              > only user. I'd make a temp database witch names the same as
                              > the session.
                              >
                              > That's why I asked this list, becouse I thought there was an
                              > more easy way to do it.
                              >
                              > MArcel.
                              >
                              > -----Oorspronkelijk bericht-----
                              > Van: SQLQueriesNoCode@yahoogroups.com
                              > [mailto:SQLQueriesNoCode@yahoogroups.com]Namens John Warner
                              > Verzonden: zondag 11 september 2005 17:58
                              > Aan: SQLQueriesNoCode@yahoogroups.com
                              > Onderwerp: RE: [SQLQueriesNoCode] A random selection out of a
                              > selection.
                              >
                              >
                              > Since you say nothing about the DBMS I will assume SQL
                              > Server, place the first result set in a temp table with a
                              > numbered key (I would use and Integer, not guid) and then use
                              > the Rand() function (see books online, creates a number
                              > between 0 and 1) and query the temp table, once you have a
                              > result drop the temp table.
                              >
                              > John Warner
                              >
                              >
                              >
                              >
                              > > -----Original Message-----
                              > > From: SQLQueriesNoCode@yahoogroups.com
                              > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Marcel Buijs
                              > > Sent: Sunday, September 11, 2005 3:13 AM
                              > > To: SQLQueriesNoCode@yahoogroups.com
                              > > Subject: [SQLQueriesNoCode] A random selection out of a selection.
                              > >
                              > >
                              > > Dear @,
                              > >
                              > > Is it possible to pick a random record out of a already made
                              > > selection?
                              > >
                              > > for example:
                              > >
                              > > I've got a music-record database.
                              > > I want to pick up a random record out of an already made
                              > > selection: Artist.
                              > >
                              > > the first part is easy:
                              > > select * from music_database where artist = ''
                              > >
                              > > many thanks in advance
                              > > Marcel.
                              > >
                              > >
                              > >
                              > >
                              > >
                              > > ------------------------ Yahoo! Groups Sponsor
                              > > --------------------~--> Fair play? Video games influencing
                              > politics.
                              > > Click and talk back!
                              > > http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/m7folB/TM
                              > > --------------------------------------------------------------
                              > > ------~->
                              > >
                              > >
                              > > Yahoo! Groups Links
                              > >
                              > >
                              > >
                              > >
                              > >
                              > >
                              > >
                              > >
                              >
                              >
                              >
                              >
                              >
                              >
                              > Yahoo! Groups Links
                              >
                              >
                              >
                              >
                              >
                              >
                              >
                              >
                              > ------------------------ Yahoo! Groups Sponsor
                              > --------------------~-->
                              > Fair play? Video games influencing politics. Click and talk
                              > back! http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/m7folB/TM
                              > --------------------------------------------------------------
                              > ------~->
                              >
                              >
                              > Yahoo! Groups Links
                              >
                              >
                              >
                              >
                              >
                              >
                              >
                              >
                              >
                            • dipankarganguly
                              Hi, You can use the ORDER BY NEWID(). I have given below a small code snippet. Do let me if it works for you. (Assumption: You want to do it in SQL Server
                              Message 14 of 26 , Sep 15, 2005
                                Hi,
                                You can use the ORDER BY NEWID(). I have given below a small code
                                snippet. Do let me if it works for you. (Assumption: You want to do
                                it in SQL Server 2000)

                                create table #dg1(Artist VARCHAR(10), Amt INT)
                                GO
                                INSERT #dg1 VALUES('A',1)
                                INSERT #dg1 VALUES('A',2)
                                INSERT #dg1 VALUES('B',1)
                                INSERT #dg1 VALUES('B',2)
                                Go
                                SELECT TOP 1 * FROM(
                                SELECT * FROM #dg1 WHERE Artist = 'A') t1
                                ORDER BY NEWID()
                                GO
                                DROP TABLE #dg1
                                GO

                                Regards,
                                Dipankar

                                --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
                                wrote:
                                > Well I gave you functions and data types in TSQL (SQL Server's SQL
                                > variant). As I said you didn't mention the DBMS, so I took the
                                easiest
                                > route <grin>
                                >
                                > John Warner
                                >
                                >
                                >
                                >
                                > > -----Original Message-----
                                > > From: SQLQueriesNoCode@yahoogroups.com
                                > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Marcel
                                Buijs
                                > > Sent: Monday, September 12, 2005 3:24 PM
                                > > To: SQLQueriesNoCode@yahoogroups.com
                                > > Subject: RE: [SQLQueriesNoCode] A random selection out of a
                                selection.
                                > >
                                > >
                                > > Dear John,
                                > >
                                > > I thougth that the list was SQLQueriesNoCode..
                                > >
                                > > I thougth about this option, but it only works when I am the
                                > > only user. I'd make a temp database witch names the same as
                                > > the session.
                                > >
                                > > That's why I asked this list, becouse I thought there was an
                                > > more easy way to do it.
                                > >
                                > > MArcel.
                                > >
                                > > -----Oorspronkelijk bericht-----
                                > > Van: SQLQueriesNoCode@yahoogroups.com
                                > > [mailto:SQLQueriesNoCode@yahoogroups.com]Namens John Warner
                                > > Verzonden: zondag 11 september 2005 17:58
                                > > Aan: SQLQueriesNoCode@yahoogroups.com
                                > > Onderwerp: RE: [SQLQueriesNoCode] A random selection out of a
                                > > selection.
                                > >
                                > >
                                > > Since you say nothing about the DBMS I will assume SQL
                                > > Server, place the first result set in a temp table with a
                                > > numbered key (I would use and Integer, not guid) and then use
                                > > the Rand() function (see books online, creates a number
                                > > between 0 and 1) and query the temp table, once you have a
                                > > result drop the temp table.
                                > >
                                > > John Warner
                                > >
                                > >
                                > >
                                > >
                                > > > -----Original Message-----
                                > > > From: SQLQueriesNoCode@yahoogroups.com
                                > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Marcel
                                Buijs
                                > > > Sent: Sunday, September 11, 2005 3:13 AM
                                > > > To: SQLQueriesNoCode@yahoogroups.com
                                > > > Subject: [SQLQueriesNoCode] A random selection out of a
                                selection.
                                > > >
                                > > >
                                > > > Dear @,
                                > > >
                                > > > Is it possible to pick a random record out of a already made
                                > > > selection?
                                > > >
                                > > > for example:
                                > > >
                                > > > I've got a music-record database.
                                > > > I want to pick up a random record out of an already made
                                > > > selection: Artist.
                                > > >
                                > > > the first part is easy:
                                > > > select * from music_database where artist = ''
                                > > >
                                > > > many thanks in advance
                                > > > Marcel.
                                > > >
                                > > >
                                > > >
                                > > >
                                > > >
                                > > > ------------------------ Yahoo! Groups Sponsor
                                > > > --------------------~--> Fair play? Video games influencing
                                > > politics.
                                > > > Click and talk back!
                                > > > http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/m7folB/TM
                                > > > --------------------------------------------------------------
                                > > > ------~->
                                > > >
                                > > >
                                > > > Yahoo! Groups Links
                                > > >
                                > > >
                                > > >
                                > > >
                                > > >
                                > > >
                                > > >
                                > > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > > Yahoo! Groups Links
                                > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > > ------------------------ Yahoo! Groups Sponsor
                                > > --------------------~-->
                                > > Fair play? Video games influencing politics. Click and talk
                                > > back! http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/m7folB/TM
                                > > --------------------------------------------------------------
                                > > ------~->
                                > >
                                > >
                                > > Yahoo! Groups Links
                                > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > >
                              Your message has been successfully submitted and would be delivered to recipients shortly.