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 !!

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 ;"

I use Microsoft Access 2000
I have already tried your sql but it doesn't work !!

"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 ..."

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)'.

OK, so that's something SQL Server can't handle for some reason. What about the "TOP 3" syntax then?

How can I write sql. If I use top synteax. I quite new in sql :-)
Please suggest !!

Try asking in the SQL Server forum. I don't know SQL Server.

The Jet database engine used by MS-Access isn't quite standard SQL, but it ought to process:-PatP

the jet engine cannot do COUNT DISTINCT

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 :-(

contact the help line, the database is broken