Professional Web Applications Themes

NEWBIE 'SELECT' QUESTION - Microsoft SQL / MS SQL Server

HI ALL I have a table store the horse racing records, each horse have over 10 racing records. I want to 'SELECT' only most recently(race_date) 8 rcords of all horses. I got no idea start from which command. Pls help me Thanks Kelvin...

  1. #1

    Default NEWBIE 'SELECT' QUESTION

    HI ALL

    I have a table store the horse racing records, each horse
    have over 10 racing records.

    I want to 'SELECT' only most recently(race_date) 8 rcords
    of all horses.

    I got no idea start from which command.

    Pls help me

    Thanks

    Kelvin
    kelvinfun Guest

  2. #2

    Default NEWBIE 'SELECT' QUESTION

    You can use the Top Clause in your query. Something like
    this:

    SELECT TOP 10 * FROM tblHorseRecords ORDER BY race_date
    DESC

    Hope this solve the purpose
    Anand

     
    Anand Guest

  3. #3

    Default Re: NEWBIE 'SELECT' QUESTION

    Not sure from your wording, are you looking are a result set of a total of 8
    rows out of the entire table? Or are you wanting the most recent 8 rows for
    each and every horse in the table? Like this:

    Horse A; Race Most Recent
    Horse A; Race Most Recent - 1
    Horse A; Race Most Recent - 2
    Horse A; Race Most Recent - 3
    Horse A; Race Most Recent - 4
    Horse A; Race Most Recent - 5
    Horse A; Race Most Recent - 6
    Horse A; Race Most Recent - 7
    Horse B; Race Most Recent
    Horse B; Race Most Recent - 1
    Horse B; Race Most Recent - 2
    Horse B; Race Most Recent - 3
    Horse B; Race Most Recent - 4
    Horse B; Race Most Recent - 5
    Horse B; Race Most Recent - 6
    Horse B; Race Most Recent - 7


    --
    Steve Troxell
    Krell Software
    Professional Database Development Tools for MS SQL Server
    http://www.krell-software.com


    "kelvinfun" <com> wrote in message
    news:092e01c35652$024550f0$gbl... 


    Steve Guest

  4. #4

    Default Re: NEWBIE 'SELECT' QUESTION

    kelvin
    SELECT TOP 8 colname FROM <your table> ORDER BY colname DESC


    "kelvinfun" <com> wrote in message
    news:092e01c35652$024550f0$gbl... 


    Uri Guest

  5. #5

    Default Re: NEWBIE 'SELECT' QUESTION

    perhaps something like the following?

    CREATE TABLE #races (
    horse CHAR(10)
    , racedate DATETIME

    , PRIMARY KEY ( horse , racedate )
    )


    INSERT #races ( horse , racedate ) VALUES ( 'A' , '20030730' )
    INSERT #races ( horse , racedate ) VALUES ( 'A' , '20030729' )
    INSERT #races ( horse , racedate ) VALUES ( 'A' , '20030728' )
    INSERT #races ( horse , racedate ) VALUES ( 'A' , '20030727' )
    INSERT #races ( horse , racedate ) VALUES ( 'A' , '20030726' )
    INSERT #races ( horse , racedate ) VALUES ( 'A' , '20030725' )
    INSERT #races ( horse , racedate ) VALUES ( 'A' , '20030724' )
    INSERT #races ( horse , racedate ) VALUES ( 'A' , '20030723' )
    INSERT #races ( horse , racedate ) VALUES ( 'A' , '20030722' )
    INSERT #races ( horse , racedate ) VALUES ( 'A' , '20030721' )
    INSERT #races ( horse , racedate ) VALUES ( 'A' , '20030720' )

    INSERT #races ( horse , racedate ) VALUES ( 'B' , '20030701' )
    INSERT #races ( horse , racedate ) VALUES ( 'B' , '20030601' )
    INSERT #races ( horse , racedate ) VALUES ( 'B' , '20030501' )
    INSERT #races ( horse , racedate ) VALUES ( 'B' , '20030401' )
    INSERT #races ( horse , racedate ) VALUES ( 'B' , '20030301' )
    INSERT #races ( horse , racedate ) VALUES ( 'B' , '20030201' )
    INSERT #races ( horse , racedate ) VALUES ( 'B' , '20030101' )
    INSERT #races ( horse , racedate ) VALUES ( 'B' , '20021201' )

    INSERT #races ( horse , racedate ) VALUES ( 'C' , '20021201' )
    INSERT #races ( horse , racedate ) VALUES ( 'C' , '20021101' )


    SELECT
    COUNT(*) AS RACES_AGO
    , r1.horse , r1.racedate
    FROM #races AS r1
    INNER JOIN #races AS r2
    ON r1.horse = r2.horse
    AND r1.racedate <= r2.racedate
    GROUP BY r1.horse , r1.racedate
    HAVING COUNT(*) <= 3




    "Steve Troxell" <spamBgone.com> wrote in message news:phx.gbl...
    Not sure from your wording, are you looking are a result set of a total of 8
    rows out of the entire table? Or are you wanting the most recent 8 rows for
    each and every horse in the table? Like this:

    Horse A; Race Most Recent
    Horse A; Race Most Recent - 1
    Horse A; Race Most Recent - 2
    Horse A; Race Most Recent - 3
    Horse A; Race Most Recent - 4
    Horse A; Race Most Recent - 5
    Horse A; Race Most Recent - 6
    Horse A; Race Most Recent - 7
    Horse B; Race Most Recent
    Horse B; Race Most Recent - 1
    Horse B; Race Most Recent - 2
    Horse B; Race Most Recent - 3
    Horse B; Race Most Recent - 4
    Horse B; Race Most Recent - 5
    Horse B; Race Most Recent - 6
    Horse B; Race Most Recent - 7


    --
    Steve Troxell
    Krell Software
    Professional Database Development Tools for MS SQL Server
    http://www.krell-software.com


    "kelvinfun" <com> wrote in message
    news:092e01c35652$024550f0$gbl... 



    Damien Guest

Similar Threads

  1. Replies: 5
    Last Post: December 3rd, 10:44 AM
  2. Pen Tool Use Question. (Embarrassingly Newbie Question)
    By Bozo Schmozo in forum Macromedia Flash
    Replies: 0
    Last Post: November 12th, 10:00 PM
  3. Newbie Question about datagrid select
    By SStory in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 22nd, 04:28 PM
  4. datagrid select ? (newbie)
    By SStory in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 22nd, 04:07 PM
  5. Help a newbie! How do I SELECT using a string as a WHERE clause?
    By Pablo Contreras in forum ASP Database
    Replies: 1
    Last Post: July 21st, 05:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139