top 20

[kovan]

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

[barrk]

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.

[kovan]

thanks :)

[barrk]

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

[Lafor]

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

[barrk]

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

[Lafor]

Thanks

[barrk]

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!

[paulw]

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.

[kovan]

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

[paulw]

I believe you.

Cheers,

P.

[SkiNLaB]

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