July 17, 2015 at 11:58 am
Creating a view on a table using:
CREATE VIEW [dbo].[vSHLBHL_BHLPriority]
AS
SELECT ID_Wells, ID_SHLBHL, Req_Fin_Priority AS MinPriority, Req_Fin, SHLBHL
FROM dbo.vSHLBHL_BHL
WHERE (Req_Fin_Priority =
(SELECT MIN(Req_Fin_Priority) AS Expr1
FROM dbo.vSHLBHL_BHL AS Sub_1_1
WHERE (dbo.vSHLBHL_BHL.ID_Wells = ID_Wells)))
The view returns the Min Priority (a ranking of importance) correctly.
However, there are a few ID_Wells where the BHL has two Min Priority (rankings) that are the same.
In this case, I want the highest ID_SHLBHL. Otherwise, there are two ID_Wells returned.
How would the view above be modified to return only 1 ID_Well where Ranking is the same, based on highest value ID_SHLBHL
Example of the Duplicates returned - 2 ID_Wells - the highest ID_SHLBHL should be the sigle one returned for each ID_Well.
CREATE TABLE [dbo].[ATestBHL](
[ID_Wells] [int] IDENTITY(1,1) NOT NULL,
[ID_SHLBHL] [int] NOT NULL,
[MinPriority] [int] NOT NULL,
[Req_Fin] [nvarchar](10) NOT NULL,
[SHLBHL] [nvarchar](3) NOT NULL
)
INSERT [dbo].[ATestBHL]([ID_Wells], [ID_SHLBHL], [MinPriority], [Req_Fin]) VALUES (2889,1576,6,'BHL')
INSERT [dbo].[ATestBHL]([ID_Wells], [ID_SHLBHL], [MinPriority], [Req_Fin]) VALUES (2889,2576,6,'BHL')
INSERT [dbo].[ATestBHL]([ID_Wells], [ID_SHLBHL], [MinPriority], [Req_Fin]) VALUES (2989,1276,5,'BHL')
INSERT [dbo].[ATestBHL]([ID_Wells], [ID_SHLBHL], [MinPriority], [Req_Fin]) VALUES (2989,3476,5,'BHL')
-- Above are have same ID_Wells - but are lower priority - 1 is highest priority - pairs below have Same ID_Well and Same Priority
INSERT [dbo].[ATestBHL]([ID_Wells], [ID_SHLBHL], [MinPriority], [Req_Fin]) VALUES (2889,4576,5,'BHL')
INSERT [dbo].[ATestBHL]([ID_Wells], [ID_SHLBHL], [MinPriority], [Req_Fin] ) VALUES (2889,4577,5,'BHL')
INSERT [dbo].[ATestBHL]([ID_Wells], [ID_SHLBHL], [MinPriority], [Req_Fin] ) VALUES (2989,5576,3,'BHL')
INSERT [dbo].[ATestBHL]([ID_Wells], [ID_SHLBHL], [MinPriority], [Req_Fin] ) VALUES (2989,6576,3,'BHL')
INSERT [dbo].[ATestBHL]([ID_Wells], [ID_SHLBHL], [MinPriority], [Req_Fin] ) VALUES (3089,7576,3,'BHL')
INSERT [dbo].[ATestBHL]([ID_Wells], [ID_SHLBHL], [MinPriority], [Req_Fin] ) VALUES (3089,8576,3,'BHL')
---- ID_Wells is PK - Want only highest ID_SHLBHL for each ID_Wells
July 17, 2015 at 12:17 pm
I'm not sure if I understood correctly, but this should give you an idea.
CREATE VIEW [dbo].[vSHLBHL_BHLPriority]
AS
WITH vSHLBHL_BHLrows AS(
SELECT ID_Wells,
ID_SHLBHL,
Req_Fin_Priority AS MinPriority,
Req_Fin,
SHLBHL,
ROW_NUMBER() OVER( PARTITION BY ID_Wells ORDER BY Req_Fin_Priority, ID_SHLBHL DESC) rn
FROM dbo.vSHLBHL_BHL
)
SELECT ID_Wells,
ID_SHLBHL,
MinPriority,
Req_Fin,
SHLBHL
FROM vSHLBHL_BHLrows
WHERE rn = 1
July 17, 2015 at 12:24 pm
Will try that first thing Monday Morning
OVER( PARTITION BY
Would have never figured that out on my own! Thanks!
July 17, 2015 at 12:31 pm
You might want to read more about the OVER clause.
Have a nice weekend 😉
July 20, 2015 at 9:26 am
Beautiful!!:w00t:
Thank you so much! I read several articles on the subject. As a Newbie, this one was the most useful to me.
http://www.midnightdba.com/Jen/2010/10/tip-over-and-partition-by/#comments
This is posted for Newbies who search on the subject after me.
My result count came out perfect!
As an Access programmer, this is set up as a View - that links two other views.
The other two linked view include custom Scalar-Valued Functions to replace VBA code on the client side of the ODBC layer
The resultant view is used as a DSN-Less Linked Table back to MS Access using SQL Server Native Client 11.0.
The resultant view is just a Linked Table on the MS Access side.
The MS Access client query can run filters provided by the user against the primary keys.
The former client side VBA Functions, MS Access Query use to take minutes.
Now the SQL Server solution takes parts of seconds.
Taking the time to learn the TSQL has had huge payoffs.
Thanks again.
I would have never thought of this solution. It has already been used several times this morning.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply