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

RE: [SQLQueriesNoCode] A random selection out of a selection.

Expand Messages
  • 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 1 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 2 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.