March 16, 2017 at 3:30 am
SERVERS DB's SIZES DATES RANKING
ServerA DB1 14642 2017-03-05 06:00:13.193 1
ServerA DB1 14497 2017-02-26 06:00:11.573 2
ServerA DB1 14381 2017-02-19 06:00:10.997 3
ServerA DB1 14354 2017-02-16 21:52:21.223 4
ServerA DB2 366 2017-03-05 06:00:13.193 1
ServerA DB2 366 2017-02-26 06:00:11.577 2
ServerA DB2 366 2017-02-19 06:00:11.003 3
ServerA DB2 366 2017-02-16 21:52:21.223 4
ServerA DB2 366 2017-02-16 21:46:15.167 5
ServerA DB2 366 2017-02-16 21:45:13.493 6
ServerA DB2 366 2017-02-16 21:42:08.730 7
ServerB DB3 42 2017-03-05 06:00:13.213 1
ServerB DB3 42 2017-02-26 06:00:11.577 2
ServerB DB4 5 2017-03-05 06:00:13.223 1
ServerB DB4 5 2017-02-26 06:00:11.577 2
ServerB DB4 5 2017-02-19 06:00:11.013 3
ServerB DB4 5 2017-02-16 21:52:21.223 4
ServerB DB4 5 2017-02-16 21:46:15.167 5
ServerB DB4 5 2017-02-16 21:45:13.493 6
ServerB DB4 5 2017-02-16 21:42:08.730 7
ServerB DB4 5 2017-01-05 23:09:17.933 8
ServerB DB4 5 2017-01-05 23:07:00.230 9
ServerB DB4 5 2017-01-05 23:05:05.463 10
ServerC DB5 7919 2017-03-05 06:00:13.240 1
ServerC DB5 7919 2017-02-26 06:00:11.580 2
ServerC DB5 7919 2017-02-19 06:00:11.013 3
I want the result to have MIN RANKING valued row for each SERVERS,DB's pair and MAX RANKING valued row for each SERVERS,DB's pair.
How do I get this result a below.
Result:
ServerA DB1 14642 2017-03-05 06:00:13.193 1
ServerA DB1 14354 2017-02-16 21:52:21.223 4
ServerA DB2 366 2017-03-05 06:00:13.193 1
ServerA DB2 366 2017-02-16 21:42:08.730 7
ServerB DB3 42 2017-03-05 06:00:13.213 1
ServerB DB3 42 2017-02-26 06:00:11.577 2
ServerB DB4 5 2017-03-05 06:00:13.223 1
ServerB DB4 5 2017-01-05 23:05:05.463 10
ServerC DB5 7919 2017-03-05 06:00:13.240 1
ServerC DB5 7919 2017-02-19 06:00:11.013 3
Thanks
March 16, 2017 at 3:46 am
Without consumable Sample data or DDL, here's something to get off your feet:WITH ServerList AS (
SELECT {Your Columns},
ROW_NUMBER() OVER (PARTITION BY [Server], DB ORDER BY Ranking ASC) AS AscRN,
ROW_NUMBER() OVER (PARTITION BY [Server], DB ORDER BY Ranking DESC) AS DescRN
FROM [YourTable])
SELECT {Required Columns}
FROM ServerList SL
WHERE SL.AscRN = 1 OR SL.DescRN = 1
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 16, 2017 at 4:08 am
Thom A - Thursday, March 16, 2017 3:46 AMWithout consumable Sample data or DDL, here's something to get off your feet:WITH ServerList AS (
SELECT {Your Columns},
ROW_NUMBER() OVER (PARTITION BY [Server], DB ORDER BY Ranking ASC) AS AscRN,
ROW_NUMBER() OVER (PARTITION BY [Server], DB ORDER BY Ranking DESC) AS DescRN
FROM [YourTable])
SELECT {Required Columns}
FROM ServerList SL
WHERE SL.AscRN = 1 OR SL.DescRN = 1
Can we do this without CTE's
March 16, 2017 at 4:36 am
sqlnewbie17 - Thursday, March 16, 2017 4:08 AMThom A - Thursday, March 16, 2017 3:46 AMWithout consumable Sample data or DDL, here's something to get off your feet:WITH ServerList AS (
SELECT {Your Columns},
ROW_NUMBER() OVER (PARTITION BY [Server], DB ORDER BY Ranking ASC) AS AscRN,
ROW_NUMBER() OVER (PARTITION BY [Server], DB ORDER BY Ranking DESC) AS DescRN
FROM [YourTable])
SELECT {Required Columns}
FROM ServerList SL
WHERE SL.AscRN = 1 OR SL.DescRN = 1Can we do this without CTE's
Yes, but why? You could change the above to use a subselect instead. otherwise you'll need to do 2 select statements in your WHERE clause to check if the value is the MAX or MIN. But a CTE would be faster.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 16, 2017 at 8:45 am
I need this to work across all SQLVersions
March 16, 2017 at 8:57 am
sqlnewbie17 - Thursday, March 16, 2017 8:45 AMI need this to work across all SQLVersions
How far back in versions are you going then, considering that 2008 is the oldest currently supported SQL Server version (which supports CTEs).
You've posted in the 2008 forums, hence why I provided a CTE. Should I instead be supplying SQL Server 2000 compliant code as you still have those instances? If the oldest version you have is 2008, then the above will work fine for all of your instances.
P.S. if you do still have 2005 or prior instances, are you considering upgrading? As I stated above, these are not supported by Microsoft any more, and databases are unlikely to perform as expected if you attempt to restore them to a SQL 2016 (or newer) Instance.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 16, 2017 at 9:49 am
Thom A - Thursday, March 16, 2017 8:57 AMsqlnewbie17 - Thursday, March 16, 2017 8:45 AMI need this to work across all SQLVersionsHow far back in versions are you going then, considering that 2008 is the oldest currently supported SQL Server version (which supports CTEs).
You've posted in the 2008 forums, hence why I provided a CTE. Should I instead be supplying SQL Server 2000 compliant code as you still have those instances? If the oldest version you have is 2008, then the above will work fine for all of your instances.
P.S. if you do still have 2005 or prior instances, are you considering upgrading? As I stated above, these are not supported by Microsoft any more, and databases are unlikely to perform as expected if you attempt to restore them to a SQL 2016 (or newer) Instance.
Pretty sure SQL Server 2005 supports CTE's as well. Been using them for years.
Edit: In fact I know it does.
March 16, 2017 at 10:00 am
Lynn Pettis - Thursday, March 16, 2017 9:49 AMPretty sure SQL Server 2005 supports CTE's as well. Been using them for years.
Edit: In fact I know it does.
Wasn't saying it didn't, just that 2008 does 🙂
IIRC they were introduced with 2005 , hence why I asked if the SQL needs to be 2000 compliant (as otherwise CTEs are still relevant). And if it is a 2000 Server, why, sqlnewbie17, have you not upgraded it? A quick Google says support for SQL Server 2000 SP4 ended about 4 years ago!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 16, 2017 at 12:04 pm
ROW_NUMBER() is not gonna work prior to SQL 2005 either anyway :-).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 16, 2017 at 4:08 pm
Here is hardcode SQL for you:SELECT SL.{Required Columns}
FROM ServerList SL
INNER JOIN (
SELECT [Server],[DB's], MIN(Ranking) as MinRanking, MAX(Ranking) as MaxRanking
FROM [YourTable]
GROUP BY [Server],[DB's]
) M ON M.[Server] = SL.[Server] AND M.[DB's] = SL.[DB's]
AND (M.MinRanking = SL.Ranking OR M.MaxRanking = SL.Ranking)
Surely will work on 2000, should do on 6.5 too.
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply