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

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

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