SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 no repeat
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rliss
Starting Member

31 Posts

Posted - 08/03/2006 :  17:14:11  Show Profile  Reply with Quote
Query:

select top 10
[LINKSET_NAME]
,[START_DATE_TIME]
,[HIGH_PCT_OCCUPANCY]
FROM [LinkOccupancy].[dbo].[LINKSET_DAILY_HIGH_VIEW]
where start_date_time >= '3/1/2006' and start_date_time <= '3/31/2006'
order by high_pct_occupancy desc

Results:

LINKSET_NA START_DATE_TIME PCT_O
------------ ----------------------------- -----
LINKSET010 2006-03-10 20:15:00.000 47.73
LINKSET007 2006-03-19 07:00:00.000 47.69
LINKSET004 2006-03-07 09:30:00.000 47.61
LINKSET004 2006-03-18 16:45:00.000 47.60
LINKSET010 2006-03-30 14:00:00.000 47.52
LINKSET005 2006-03-05 15:30:00.000 47.48
LINKSET010 2006-03-09 02:15:00.000 47.45
LINKSET012 2006-03-08 09:00:00.000 47.44
LINKSET003 2006-03-18 23:30:00.000 47.43
LINKSET006 2006-03-13 09:00:00.000 47.38

Is there a way to keep a linkset_name from repeating? distinct doesn't work.

Thanks,

RLiss

Edited by - Rliss on 08/03/2006 17:39:47

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 08/03/2006 :  17:51:42  Show Profile  Reply with Quote
"Is there a way to keep a linkset_name from repeating? distinct doesn't work."
What is your expected result ?


KH

Go to Top of Page

Rliss
Starting Member

31 Posts

Posted - 08/03/2006 :  17:58:30  Show Profile  Reply with Quote
The result I want is the top 10 Linksets ( the 10 linksets with the highest occupancy, 10 different linksets, it is not a true top 10 list when I get 7 linksets where some of them repeat)

RLiss
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 08/03/2006 :  18:18:16  Show Profile  Reply with Quote
select top 10
[LINKSET_NAME]
,[START_DATE_TIME]
,[HIGH_PCT_OCCUPANCY]
FROM [LinkOccupancy].[dbo].[LINKSET_DAILY_HIGH_VIEW]
where start_date_time >= '3/1/2006' and start_date_time <= '3/31/2006'
GROUP BY LINKSET_NAME
order by high_pct_occupancy desc





KH

Go to Top of Page

Rliss
Starting Member

31 Posts

Posted - 08/03/2006 :  18:21:17  Show Profile  Reply with Quote
I apologize, I also need the corresponding data for the linkset...
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 08/03/2006 :  18:27:20  Show Profile  Reply with Quote
But which one do you need ? the one with max (high_pct_occupancy desc) ?
LINKSET010 2006-03-10 20:15:00.000 47.73
LINKSET010 2006-03-30 14:00:00.000 47.52
LINKSET010 2006-03-09 02:15:00.000 47.45


KH

Go to Top of Page

Rliss
Starting Member

31 Posts

Posted - 08/03/2006 :  18:39:56  Show Profile  Reply with Quote
yes, the one with the max(high_occupancy)

RLiss
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 08/03/2006 :  19:41:22  Show Profile  Reply with Quote
try this. . .
select	[LINKSET_NAME], [START_DATE_TIME], [HIGH_PCT_OCCUPANCY]
from	[LinkOccupancy].[dbo].[LINKSET_DAILY_HIGH_VIEW]
inner join
(
	select	top 10  [LINKSET_NAME], max(high_pct_occupancy) as max_occupancy
	from	@table
        where   . . . 
	group by  [LINKSET_NAME]
	order by max(high_pct_occupancy) desc
) m
on	t.[LINKSET_NAME]	= m.[LINKSET_NAME]
and	t.high_pct_occupancy	= m.max_occupancy



KH

Go to Top of Page

Rliss
Starting Member

31 Posts

Posted - 08/04/2006 :  11:26:16  Show Profile  Reply with Quote
This worked... now I have to clean it up..

select a.[LINKSET_NAME], a.[START_DATE_TIME], a.[HIGH_PCT_OCCUPANCY]
from [LinkOccupancy].[dbo].[LINKSET_DAILY_HIGH_VIEW] a
inner join
(
select top 10 m.[LINKSET_NAME], max(m.high_pct_occupancy) as max_occupancy
from [LinkOccupancy].[dbo].[LINKSET_DAILY_HIGH_VIEW] as m
where m.start_date_time >= '3/1/2006' and m.start_date_time <= '3/31/2006'
group by m.[LINKSET_NAME]
order by max(m.high_pct_occupancy) desc
) t
on a.[LINKSET_NAME] = t.[LINKSET_NAME]
and a.high_pct_occupancy = t.max_occupancy
where a.start_date_time >= '3/1/2006' and a.start_date_time <= '3/31/2006'
order by a.[HIGH_PCT_OCCUPANCY] desc

I had to duplicate the where clause because I picked up a record from a different date range with the same pct_occupancy, a highly unlikely occurance, but better to catch this in test.

Thanks...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000