select top issue.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select top issue..

Author

Topic

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-12-19 : 17:15:12

Hi,

I looking for a way to select top 3 post, which basicly is easy, but this select top is a bit different.

In my database i have a column which contains hits for different products, but when I do a select top 3 f.ex. It will select the top something instead of the actual top 3.

Let me make an example.

Select top 3 from products order by producthits desc

will f.ex. select the top 6 products because there is 1 product, which have been viewed 30 times the rest have been viewed lets say 20 times.

Do any of you know of a way to only select the max top 3 of records ?

Best regards
Taz

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-19 : 18:20:00

Select top 3 * from products order by producthits desc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-19 : 22:42:48

1 You missed to specify column names in Select top 3 from products order by producthits desc
2 If not post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-12-20 : 03:50:21

sorry guys.. here goes.

my current select is : SELECT TOP 3 * FROM PRODUCTS ORDER BY ProductHits DESC

The result from that select is:

productname/producthits
bucket / 4
paint / 3
glass / 3
plug / 3
chair / 3
candle / 3

But what i'm looking for is this result.
bucket / 4
paint / 3
glass / 3

Best regards
Taz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 04:17:25

The query suggested to you works EXACTLY as you wish for.

PLEASE REMOVE "WITH TIES" KEYWORD.

Peter Larsson
Helsingborg, Sweden

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-12-20 : 04:25:37

Hi Peso,

No it doesn't unfornately, i get the result with the 6 products.

what do you mean by ?

quote:
PLEASE REMOVE "WITH TIES" KEYWORD

Best regards
Taz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 04:37:54

See the difference in output between these three queries.
The second one works exactly as you want to.
Copy and paste code in Query Analyzer as is without changes

-- prepare test data
declare @products table (productname varchar(6), producthits tinyint)

insert	@products
select	'bucket', 4 union all
select	'paint', 3 union all
select	'chair', 3 union all
select	'candle', 3 union all
select	'plug', 2

-- do the work
select * from @products order by producthits desc

select top 3 * from @products order by producthits desc

select top 3 with ties * from @products order by producthits desc

See the difference in both code and output?

Peter Larsson
Helsingborg, Sweden

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-12-20 : 05:22:33

Hi Peso,

Thanks alot, just checked with sql server and the top 3 is executed as I expected ( only the top 3 records )

Sorry I failed to mention, that my database is access

somehow access doesn't take the select top 3 serious

Best regards
Taz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 05:35:00

Please post MS ACCESS queries in the proper forum in the future.
Have you found a solution for ACCESS yet?

Peter Larsson
Helsingborg, Sweden

TazMania
Yak Posting Veteran

63 Posts

Posted - 2006-12-20 : 05:38:01

Hi again,

I'll do remember that - sorry - will not happen again.

Apparently access uses the with ties instead of the actual top 3, frustating

hmm I think i'll recommend my client to switch to sql server.. anyways found a workaround - SELECT TOP 3 * FROM (select * from products ORDER BY products.productHits DESC) which works like a charm

Thanks for a great inspiration..

Best regards
Taz

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-20 : 07:21:42

I wonder why Top operator doesnt work in ACCESS as expected

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources