top 20

i just wwant to retrieve top 20 records based on number of hits
something like SQL = "SELECT * FROM MY TABLE where hits >0 TOP 20"
whats best way of getting the 20 records with a WHERE clause

select top 20 <selectlist> from mytable where <hitfield> > 20

top n[percent] is also acceptable if you aren't looking for only 20 records but want to limit the size of your output.

thanks :)

You are welcome!!!!!!!!!

can i use that in sql server 6.5

can't seem to get it to work

Think I am missing something

getting the error

incorrect syntax near 10

when I do
select top 10 from tblone

You need "select top 10 whatevers(insert you fieldname here) from tableone" in SQL 7.0

Thanks

Did you try adding the fieldnames to your SQL statement and running it on SQL 6.5? I'd give that a try before I gave up!

TOP will return a fairly arbitrary set of records that satisfy the criteria unless you also use ORDER BY.

For instance, say you want the five records with most hits and hits > 5.

e.g.
ID Hits
1 10
2 5
3 4
4 7
5 6
6 12
7 18
8 2
9 23
10 1

"SELECT TOP 5 ID FROM MyTable WHERE HITS > 5" might return ID's 1, 5, 4, 9 and 7 - ID 6 should be included but you have not specified an order, so the SELECT will simply return the first 5 records it finds.

"SELECT TOP 5 ID FROM MyTable WHERE HITS > 5 ORDER BY Hits" will return
9, 7, 6, 1, 4 - That's better.

Incidentally, note that TOP will not distinguish between ties, so if the table was:

ID Hits
1 10
2 7
3 4
4 7
5 7
6 12
7 18
8 2
9 23
10 1

"SELECT TOP 5 ID FROM MyTable WHERE HITS > 5 ORDER BY Hits" will return
9, 7, 6, 1, 2, 4, 5 - seven records, despite requesting 5! What you are really saying is up to 5 and ties.

i already had ORDER BY
most of my sql statements got one of those hehe

I believe you.

Cheers,

P.

well i need to use TOP 100 and i NEED an order by but i DONT want ties!!! stupid ties! any help?