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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
17689 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
17689 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
17689 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
17689 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  
 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.04 seconds. Powered By: Snitz Forums 2000