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

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

Expand Messages
  • 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 1 of 26 , Aug 16, 2005
    • 0 Attachment
      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 2 of 26 , Aug 17, 2005
      • 0 Attachment
        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 3 of 26 , Sep 11, 2005
        • 0 Attachment
          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 4 of 26 , Sep 11, 2005
          • 0 Attachment
            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 5 of 26 , Sep 11, 2005
            • 0 Attachment
              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 6 of 26 , Sep 12, 2005
              • 0 Attachment
                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 7 of 26 , Sep 12, 2005
                • 0 Attachment
                  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 8 of 26 , Sep 12, 2005
                  • 0 Attachment
                    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 9 of 26 , Sep 12, 2005
                    • 0 Attachment
                      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 10 of 26 , Sep 12, 2005
                      • 0 Attachment
                        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 11 of 26 , Sep 12, 2005
                        • 0 Attachment
                          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 12 of 26 , Sep 12, 2005
                          • 0 Attachment
                            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 13 of 26 , Sep 15, 2005
                            • 0 Attachment
                              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.