Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

SQL Select query

I have a table such as the table below.   The BegMTypw identifies where a particular feature begins, EndMType identifies where a particular feature ends.  CasingID is the foreignkey to the Feature.  
ID      Coordinate      BegMType      EndMType      CasingID
1      0                      Casing                                             1
2      5                  
3      7                  
4      8                  
5      9                  
6      10                                               Casing                       1
7      20                  
8      30                  
9      35                  
10      50                       Casing                                             1
11      51                  
12      62                  
13      100                                               Casing                       1


My output should look like

BegM    EndM     CasingID
0            10            1
50           100          1

The coordinates will always be entered in order.  Any ideas on how I can get the output above?  I can't do min and max because the CasingID is not unique, this would yeild

BegM   EndM   CasingID
0             100        1

This result would be incorrect, I need the two records for beg and end.
0
yanci1179
Asked:
yanci1179
1 Solution
 
dannygonzalez09Commented:
Something like this?
SELECT * INTO #Test
FROM
(
SELECT Id = 1,CoOrdinate = 0, BegMType = 'Casing',EndMtype = '',CasingId = 1
UNION
SELECT Id = 2,CoOrdinate = 5, BegMType = '',EndMtype = '',CasingId = ''
UNION
SELECT Id = 3,CoOrdinate = 7, BegMType = '',EndMtype = '',CasingId = ''
UNION
SELECT Id = 4,CoOrdinate = 10, BegMType = '',EndMtype = 'Casing',CasingId = 1
UNION
SELECT Id = 5,CoOrdinate = 50, BegMType = 'Casing',EndMtype = '',CasingId = 1
UNION
SELECT Id = 6,CoOrdinate = 100, BegMType = '' ,EndMtype = 'Casing',CasingId = 1

)x

With BegCTE AS
(
SELECT CoOrdinate,CasingId,RANK() OVER (PARTITION BY BegMType ORDER BY Id) BegRnk FROM #Test
WHERE LEN(BegMType) > 1
)
,EndCTE AS
(
SELECT CoOrdinate,CasingId,RANK() OVER (PARTITION BY EndMType ORDER BY Id) EndRnk FROM #Test
WHERE LEN(EndMType) > 1
)

SELECT B.CoOrdinate,E.CoOrdinate,B.CasingId FROM BegCTE B
LEFT JOIN EndCTE E
ON B.BegRnk = E.EndRnk and B.CasingId = E.CasingId

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
you could look at the rank() function.

http://technet.microsoft.com/en-us/library/ms176102.aspx

it's a CTE with a join

something like


;with cte as
(select *, rank() over (Partition By CasingID order by coordinate) myRank
  from table
  where isnull(BegMType, '') != ''  or isnull(EndMType, '') != ''
)  


select  B.Coordinate, E.Coordinate, B.CasingId
from cte B
join cte E on B.CasingID = E.CasingID and E.MyRank = B.MyRank +1 and isnull(B.BegMType, '') != ''
0
 
yanci1179Author Commented:
Thanks!!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now