Selecting top 10 results using DISTINCT

[mikevde]

Hi,

Can anyone confirm whether it is possible to:

a) use subqueries in mySQL
b) use DISTINCT TOP in mySQL?

I am trying to find a way to return the top 5 results in my table. For simplicity sake:

user I hits I date

for one user, there may be several records but with different 'hits' values. e.g

userA I 15 I ddmmyy
userB I 22 I ddmmyy
userC I 20 I ddmmyy
userA I 13 I ddmmyy
userM I 17 I ddmmyy
userB I 26 I ddmmyy

So, how would extract the top 10 users but NOT repeating a user that may have duplicate records? So i only want to return the distinct 'user' value, and skip them if they occur again. i.e. i would end up with the following ranked by hits:

user I hits I date
userB 26
userC 20
userM 17
userA 15

any help with achieving this in mySQL would be fantastic. the alternative might be to force this behaviour in the application code should it not be poss in mySQL

many thanks
m

[mikevde]

Hi
Can anyone help me with this? So far no comments. Thanks[


QUOTE=mikevde]Hi,

Can anyone confirm whether it is possible to:

a) use subqueries in mySQL
b) use DISTINCT TOP in mySQL?

I am trying to find a way to return the top 5 results in my table. For simplicity sake:

user I hits I date

for one user, there may be several records but with different 'hits' values. e.g

userA I 15 I ddmmyy
userB I 22 I ddmmyy
userC I 20 I ddmmyy
userA I 13 I ddmmyy
userM I 17 I ddmmyy
userB I 26 I ddmmyy

So, how would extract the top 10 users but NOT repeating a user that may have duplicate records? So i only want to return the distinct 'user' value, and skip them if they occur again. i.e. i would end up with the following ranked by hits:

user I hits I date
userB 26
userC 20
userM 17
userA 15

any help with achieving this in mySQL would be fantastic. the alternative might be to force this behaviour in the application code should it not be poss in mySQL

many thanks
m[/quote]

[gauravgmbhr]

TRY THIS INSTEAD OF distinct top 10

SELECT * 
FROM `some table`
WHERE condition
Order BY `field that decides the top entries`
LIMIT 10;

or try

SELECT *
FROM `ur table`
WHERE (if condition exists)
ORDER BY `hits`
LIMIT 5

dont use distint coz distinct will scan the whole table
plus in ur case if u use distinct ur users with same number of hits as some other user ,will not come in ur list

[Anu1]

Hi,

Can anyone help me to get the same with SQLServer? Limit is not applicable in SQLServer.

Thanks,
Anu

[gauravgmbhr]

Hi,

Can anyone help me to get the same with SQLServer? Limit is not applicable in SQLServer.

Thanks,
Anu

Well In SQl SERVER u can use the top clause

TRY THIS

SELECT TOP 5 * FROM 'urtable'
WHERE [Some Condition];


If U wanna read More
Try this

[sdlogy]

Hey....thats an interesting thing actually........never thought of it........
Try this........

First Create a view selecting the distinct values from the table.....

create view <<View name>>
As
Select distinct from <<table name>>
where
<<filtering conditions if any>>;

Then select top 10 or 5 values from the view.

Select top 10 * from <<view name>> (nolock)
order by <<required column name >>

That way I guess you can ensure every time you select the values, you get the distinct value.

Not sure if this works......please let me know as well after trying!!


Havent used the limit command......will check it out.....

Cheers