Limiting rows - 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 2005 Forums

Transact-SQL (2005)

Limiting rows

Author

Topic

Rliss
Starting Member

31 Posts

Posted - 2007-08-22 : 16:33:38

I am using the following query to populate a view...

SELECT TOP (100) PERCENT LINKSET_NAME,
START_DATE_TIME,
START_DATE_TEXT,
TRANS_ISUP_OCT,
TRANS_SCCP_OCT,
TRANS_OTHER_OCT,
RECV_ISUP_OCT,
RECV_SCCP_OCT,
RECV_OTHER_OCT,
TRANS_BANDWIDTH,
RECV_BANDWIDTH,
HIGH_PCT_OCCUPANCY,
HIGH_TRANS_OR_RECV
FROM dbo.LINKSET_INTERVAL AS a
WHERE (
HIGH_PCT_OCCUPANCY =
(SELECT MAX(HIGH_PCT_OCCUPANCY) AS Expr1
FROM dbo.LINKSET_INTERVAL
WHERE (LINKSET_NAME = a.LINKSET_NAME) AND (START_DATE_TEXT = a.START_DATE_TEXT))
)
ORDER BY LINKSET_NAME, START_DATE_TEXT;

the subquery returns the max HIGH_PERCENT_OCCUPANCY

The Problem: in the top level select, there may be more than one record that is equal to the max. I need to limit to 1 row per linkset_name, start_date_text. could I manipulate the 'select top' and sort on start_date_time.

RLiss

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 16:37:50

[code]SELECT LINKSET_NAME,
START_DATE_TIME,
START_DATE_TEXT,
TRANS_ISUP_OCT,
TRANS_SCCP_OCT,
TRANS_OTHER_OCT,
RECV_ISUP_OCT,
RECV_SCCP_OCT,
RECV_OTHER_OCT,
TRANS_BANDWIDTH,
RECV_BANDWIDTH,
HIGH_PCT_OCCUPANCY,
HIGH_TRANS_OR_RECV
FROM (
SELECT LINKSET_NAME,
START_DATE_TIME,
START_DATE_TEXT,
TRANS_ISUP_OCT,
TRANS_SCCP_OCT,
TRANS_OTHER_OCT,
RECV_ISUP_OCT,
RECV_SCCP_OCT,
RECV_OTHER_OCT,
TRANS_BANDWIDTH,
RECV_BANDWIDTH,
HIGH_PCT_OCCUPANCY,
HIGH_TRANS_OR_RECV,
ROW_NUMBER() OVER (PARTITION BY LINKSET_NAME, START_DATE_TEXT ORDER BY HIGH_PCT_OCCUPANCY DESC) AS RecID
FROM dbo.LINKSET_INTERVAL AS a
) AS d
WHERE RecID = 1
ORDER BY LINKSET_NAME,
START_DATE_TEXT[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources