Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2004
    Posts
    77

    Unanswered: Sql (Inner Join)

    I have 2 tables. The first table name register and the second table name showrank.
    Table register

    id name username
    1 ae1 w
    2 ae2 e
    3 ae3 r
    4 ae4 t
    5 ae5 u

    Table showrank
    id totalgainloss
    1 200
    2 500
    3 100
    4 90
    5 150

    I want the result to show the first three highest totalgainloss. with name... like this

    ae2 500
    ae1 200
    ae5 150

    I tried
    SELECT register.*, showrank.totalgainloss FROM register INNER JOIN showrank ON register.id = showrank.id ORDER BY showrank.totalgainloss DESC ;"

    But the result that I got was
    ae2 500
    ae1 200
    ae5 150
    ae3 100
    ae4 90

    I want only the first three highest totalgainloss. Anyone know how to do it !!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Which DBMS? Each has its own proprietary ways of doing "top n" queries.

    However, a generic (slower) SQL solution would be something like:

    SELECT register.*, showrank.totalgainloss
    FROM register
    INNER JOIN showrank ON register.id = showrank.id
    WHERE 3 >= (SELECT COUNT(DISTINCT totalgainloss) FROM showrank s2 WHERE s2.totalgainloss >= showrank.totalgainloss)
    ORDER BY showrank.totalgainloss DESC ;"

  3. #3
    Join Date
    Aug 2004
    Posts
    77
    I use Microsoft Access 2000
    I have already tried your sql but it doesn't work !!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    "Doesn't work" in what sense? Returns too many rows? Gives a syntax error message?

    In SQL Server I believe you can do something like "SELECT TOP 3 ..."

  5. #5
    Join Date
    Aug 2004
    Posts
    77
    This is the message that I got when I type your sql

    Microsoft JET Database Engine error '80040e14'

    Syntax error. in query expression '3 >= (SELECT COUNT(DISTINCT totalgainloss) FROM showrank s2 WHERE s2.totalgainloss >= showrank.totalgainloss)'.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, so that's something SQL Server can't handle for some reason. What about the "TOP 3" syntax then?

  7. #7
    Join Date
    Aug 2004
    Posts
    77
    How can I write sql. If I use top synteax. I quite new in sql :-)
    Please suggest !!

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try asking in the SQL Server forum. I don't know SQL Server.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The Jet database engine used by MS-Access isn't quite standard SQL, but it ought to process:
    Code:
    SELECT TOP 3 register.*, showrank.totalgainloss
       FROM register
       INNER JOIN showrank ON register.id = showrank.id
       ORDER BY showrank.totalgainloss DESC ;"
    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gop373
    This is the message that I got when I type your sql

    Microsoft JET Database Engine error '80040e14'

    Syntax error. in query expression '3 >= (SELECT COUNT(DISTINCT totalgainloss) FROM showrank s2 WHERE s2.totalgainloss >= showrank.totalgainloss)'.
    the jet engine cannot do COUNT DISTINCT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2004
    Posts
    77
    I tried
    SELECT TOP 3 register.*, showrank.totalgainloss
    FROM register
    INNER JOIN showrank ON register.id = showrank.id
    ORDER BY showrank.totalgainloss DESC ;"

    but I got message HTTP 500 - Internal server error :-(

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gop373
    I got message HTTP 500 - Internal server error :-(
    contact the help line, the database is broken
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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