August 12, 2008 at 5:50 am
I have this table
CREATE TABLE NUMBER
( NumberID SMALLINT PRIMARY KEY NONCLUSTERED,
NumberValue SMALLINT NOT NULL DEFAULT '0',
CHECK (NumberValue <10),
NumberTimesUsed SMALLINT NULL
)
INSERT NUMBER VALUES ('1', '1', '5')
INSERT NUMBER VALUES ('2', '2', '4')
INSERT NUMBER VALUES ('3', '3', '8')
INSERT NUMBER VALUES ('4', '4', '11')
INSERT NUMBER VALUES ('5', '5', '1')
I need to create a view to retrieve the top3 MOST USED NUMBERS, I dont know if i can do this with the TOP n Statement?
August 12, 2008 at 6:18 am
Will this work?
CREATE VIEW NumberView AS
SELECT TOP 3 WITH TIES
count(numbervalue) AS Amt,
numbervalue
FROM number
GROUP BY numbervalue
ORDER BY count(numbervalue)
August 12, 2008 at 6:25 am
I gave it a try and it does not seem 2 work,
RESULT
Amt numbervalue
----------- -----------
1 2
1 3
1 4
1 5
1 8
(5 row(s) affected)
I only need the top3 most used numbers, so it should look like this
NumberValue NumberTimesUsed
4 11
3 8
1 5
August 12, 2008 at 6:28 am
Trevor,
Try the below code and let me know.
Select TOP 3 NumberValue,NumberTimesUsed
from Number
order by NumberOfTimesUsed desc
karthik
August 12, 2008 at 6:28 am
Sorry, only saw the third column afterwards. Also, my sort order needed some changing.
Try this & let me know:
ALTER /*CREATE*/ VIEW NumberView AS
SELECT TOP 3 WITH TIES
numberValue,
numberTimesUsed
FROM number
ORDER BY numberTimesUsed DESC
August 12, 2008 at 6:31 am
Hey Hey TenCent, Nope Not right. 😀
NumberValue NumberTimesUsed
----------- ---------------
4 0
3 0
2 0
August 12, 2008 at 6:33 am
SSC Veteran
No its still not right, this has been bugging for the whole morning,:)
Your returned result
numberValue numberTimesUsed
----------- ---------------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
7 0
6 0
1 0
August 12, 2008 at 6:35 am
Hey Trevor My name is not 'TenCent'.:D
Post the code here which you have executed.
karthik
August 12, 2008 at 6:36 am
Umm...TenCent and SSC Veteran is not the names.:P
karthik
August 12, 2008 at 6:37 am
Can you also send the results of the following:
sp_helptext numberview
August 12, 2008 at 6:41 am
sorry sorry, 😀 my mind is in 2 places here
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE /*CREATE*/ VIEW NumberView AS
SELECT TOP 3 WITH TIES
numberValue,
numberTimesUsed
FROM number
ORDER BY numberTimesUsed DESC
August 12, 2008 at 6:41 am
Hey Trevor,
I have tested the below script, It is giving perfect result.
create table #t1
(
ID int,
Used int
)
go
insert into #t1
select 1,2
union
select 2,10
union
select 3,11
union
select 4, 1
union
select 5,8
select TOP 3 ID,Used
from #t1
order by Used Desc
ID Used
311
210
58
I think somewhere else you did mistake. so post your code then only i can help you.
karthik
August 12, 2008 at 6:46 am
karthikeyan, I need to create a view outer that? is there no easier way :hehe:
My code Is On the first page,
And I dont think that list will get updated if the numbers change
August 12, 2008 at 6:54 am
karthikeyan (8/12/2008)
Hey Trevor,I have tested the below script, It is giving perfect result.
Same here.
Trevor, Karthik's script above uses a temp table - Which cannot be used in view's definitions.
August 12, 2008 at 7:08 am
yeah it does, but its way to complicated and its not really what I am looking for, There must be an easier way of doing this, I just cant think of anything, I have been trying all day:w00t:
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply