no repeat - 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

Transact-SQL (2000)

no repeat

Author

Topic

Rliss
Starting Member

31 Posts

Posted - 2006-08-03 : 17:14:11

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-03 : 17:51:42

"Is there a way to keep a linkset_name from repeating? distinct doesn't work."
What is your expected result ?

Rliss
Starting Member

31 Posts

Posted - 2006-08-03 : 17:58:30

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-03 : 18:18:16

[code]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[/code]

Rliss
Starting Member

31 Posts

Posted - 2006-08-03 : 18:21:17

I apologize, I also need the corresponding data for the linkset...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-03 : 18:27:20

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

Rliss
Starting Member

31 Posts

Posted - 2006-08-03 : 18:39:56

yes, the one with the max(high_occupancy)

RLiss

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-03 : 19:41:22

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

Rliss
Starting Member

31 Posts

Posted - 2006-08-04 : 11:26:16

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...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources