January 29, 2015 at 4:52 am
Hi,
For the following query, I am trying to fetch only one row (no duplicates) for each BL_ID based on
the logic that if I have multiple rows for one BL_ID, then only the one which has the largest LEG_SEQ_NBR should be displayed and the other rows should be ignored.
I am using the below code get to the above logic:
ROW_NUMBER() OVER (PARTITION BY ITIN.BL_ID ORDER BY ITIN.LEG_SEQ_NBR desc) AS RowNum
Can someone help me on this please.
Thanks.
select
*
FROM
(
SELECT
TMIS.[BL_ID]
,[POL_NAME]
,[POD_NAME]
,[POR_NAME]
,[LINE]
,[VESSEL]
,[VOYAGE]
,[LEG]
,ITIN.VESSEL_CD as [2ND_VESSEL]
,ITIN.VOYAGE_CD as [2ND_VOYAGE]
,ITIN.LEG_CD as [2ND_LEG]
,ITIN.Transport_Mode_Cd,ITIN.LEG_SEQ_NBR,
ROW_NUMBER() OVER (PARTITION BY ITIN.BL_ID ORDER BY ITIN.LEG_SEQ_NBR desc) AS RowNum
FROM
tbl_TMIS_DATASET TMIS
inner JOIN
MiniGapp..MG_BL_ITINERARY ITIN
ON TMIS.BL_ID = ITIN.BL_ID
) AS T
where rownum in (1,2)
January 29, 2015 at 5:35 am
pwalter83 (1/29/2015)
Hi,For the following query, I am trying to fetch only one row (no duplicates) for each BL_ID based on
the logic that if I have multiple rows for one BL_ID, then only the one which has the largest LEG_SEQ_NBR should be displayed and the other rows should be ignored.
I am using the below code get to the above logic:
ROW_NUMBER() OVER (PARTITION BY ITIN.BL_ID ORDER BY ITIN.LEG_SEQ_NBR desc) AS RowNum
Can someone help me on this please.
Thanks.
select
*
FROM
(
SELECT
TMIS.[BL_ID]
,[POL_NAME]
,[POD_NAME]
,[POR_NAME]
,[LINE]
,[VESSEL]
,[VOYAGE]
,[LEG]
,ITIN.VESSEL_CD as [2ND_VESSEL]
,ITIN.VOYAGE_CD as [2ND_VOYAGE]
,ITIN.LEG_CD as [2ND_LEG]
,ITIN.Transport_Mode_Cd,ITIN.LEG_SEQ_NBR,
ROW_NUMBER() OVER (PARTITION BY ITIN.BL_ID ORDER BY ITIN.LEG_SEQ_NBR desc) AS RowNum
FROM
tbl_TMIS_DATASET TMIS
inner JOIN
MiniGapp..MG_BL_ITINERARY ITIN
ON TMIS.BL_ID = ITIN.BL_ID
) AS T
where rownum in (1,2)
It looks correct Paul, apart from the filter
where rownum in (1,2)
which should be
WHERE rownum = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 29, 2015 at 5:49 am
ChrisM@Work (1/29/2015)
pwalter83 (1/29/2015)
Hi,For the following query, I am trying to fetch only one row (no duplicates) for each BL_ID based on
the logic that if I have multiple rows for one BL_ID, then only the one which has the largest LEG_SEQ_NBR should be displayed and the other rows should be ignored.
I am using the below code get to the above logic:
ROW_NUMBER() OVER (PARTITION BY ITIN.BL_ID ORDER BY ITIN.LEG_SEQ_NBR desc) AS RowNum
Can someone help me on this please.
Thanks.
select
*
FROM
(
SELECT
TMIS.[BL_ID]
,[POL_NAME]
,[POD_NAME]
,[POR_NAME]
,[LINE]
,[VESSEL]
,[VOYAGE]
,[LEG]
,ITIN.VESSEL_CD as [2ND_VESSEL]
,ITIN.VOYAGE_CD as [2ND_VOYAGE]
,ITIN.LEG_CD as [2ND_LEG]
,ITIN.Transport_Mode_Cd,ITIN.LEG_SEQ_NBR,
ROW_NUMBER() OVER (PARTITION BY ITIN.BL_ID ORDER BY ITIN.LEG_SEQ_NBR desc) AS RowNum
FROM
tbl_TMIS_DATASET TMIS
inner JOIN
MiniGapp..MG_BL_ITINERARY ITIN
ON TMIS.BL_ID = ITIN.BL_ID
) AS T
where rownum in (1,2)
It looks correct Paul, apart from the filter
where rownum in (1,2)
which should be
WHERE rownum = 1
Thanks Chris, that helped. I also added the order by clause, it works okay now :). Cheers !
January 29, 2015 at 8:26 am
ChrisM@Work (1/29/2015)
pwalter83 (1/29/2015)
Hi,For the following query, I am trying to fetch only one row (no duplicates) for each BL_ID based on
the logic that if I have multiple rows for one BL_ID, then only the one which has the largest LEG_SEQ_NBR should be displayed and the other rows should be ignored.
I am using the below code get to the above logic:
ROW_NUMBER() OVER (PARTITION BY ITIN.BL_ID ORDER BY ITIN.LEG_SEQ_NBR desc) AS RowNum
Can someone help me on this please.
Thanks.
select
*
FROM
(
SELECT
TMIS.[BL_ID]
,[POL_NAME]
,[POD_NAME]
,[POR_NAME]
,[LINE]
,[VESSEL]
,[VOYAGE]
,[LEG]
,ITIN.VESSEL_CD as [2ND_VESSEL]
,ITIN.VOYAGE_CD as [2ND_VOYAGE]
,ITIN.LEG_CD as [2ND_LEG]
,ITIN.Transport_Mode_Cd,ITIN.LEG_SEQ_NBR,
ROW_NUMBER() OVER (PARTITION BY ITIN.BL_ID ORDER BY ITIN.LEG_SEQ_NBR desc) AS RowNum
FROM
tbl_TMIS_DATASET TMIS
inner JOIN
MiniGapp..MG_BL_ITINERARY ITIN
ON TMIS.BL_ID = ITIN.BL_ID
) AS T
where rownum in (1,2)
It looks correct Paul, apart from the filter
where rownum in (1,2)
which should be
WHERE rownum = 1
Hi Chris,
Sorry to bother you once again. I got it all wrong in terms of logic and requirements.
The requirement is to create 3 new columns
2ND_VESSEL
2ND_VOYAGE
2ND_LEG
This would be done by fetching the LEG_SEQ_NBR by joining the 2 tables-
tbl_TMIS_DATASET & MG_BL_ITINERARY
and once we have the LEG_SEQ_NBR then increment it by 1 and then fetch the 2ND_VESSEL,
2ND_VOYAGE and 2ND_LEG from the MG_BL_ITINERARY table. If there is no row present then the 2ND_VESSEL, 2ND_VOYAGE and 2ND_LEG will be NULL.
As an example I have provided the DDL of the 2 tables and sample data below. Also, I have provided the required resultset. I don't know if this is possible using SQL only.
CREATE TABLE [dbo].[tbl_TMIS_DATASET](
[BL_ID] [decimal](10, 0) NOT NULL,
[VESSEL] [nvarchar](10) NULL,
[VOYAGE] [nvarchar](12) NULL,
[LEG] [nchar](3) NULL,
)
CREATE TABLE [dbo].[MG_BL_ITINERARY](
[BL_ID] [numeric](10, 0) NULL,
[LEG_SEQ_NBR] [numeric](3, 0) NULL,
[VESSEL_CD] [varchar](10) NULL,
[VOYAGE_CD] [varchar](12) NULL,
[LEG_CD] [char](1) NULL,
)
INSERT INTO [tbl_TMIS_DATASET]
VALUES('19516116','HJAMRC','0010E','E')
INSERT INTO MG_BL_ITINERARY
VALUES('19516116','2','HJAMRC','0010E','E')
INSERT INTO MG_BL_ITINERARY
VALUES('19516116','1','ROBERT','3814S','S')
INSERT INTO MG_BL_ITINERARY
VALUES('19516116','3','YMUNCN','08E','E')
INSERT INTO MG_BL_ITINERARY
VALUES('19516116','4','JTWR','002S','S')
Based on the above data if we join the 2 tables using the below query, then we get the LEG_SEQ_NBR as 2.
SELECT
TMIS.[BL_ID]
,[VESSEL]
,[VOYAGE]
,[LEG]
, LEG_SEQ_NBR
FROM tbl_TMIS_DATASET TMIS
INNER JOIN MG_BL_ITINERARY ITIN
ON TMIS.BL_ID = ITIN.BL_ID
and TMIS.VESSEL = ITIN.VESSEL_CD
and TMIS.VOYAGE = ITIN.VOYAGE_CD
and TMIS.LEG = ITIN.LEG_CD
We then increment the LEG_SEQ_NBR (2) by 1 which is 2 + 1 = 3. Our new LEG_SEQ_NBR is now 3 and we now go into MG_BL_ITINERARY table and look for the row with LEG_SEQ_NBR = 3. We get these values - 'YMUNCN','08E','E' as the 2ND_VESSEL, 2ND_VOYAGE, 2ND_LEG.
I hope I have been able to explain the issue in detail. I am stuck on this for a long time now and have no clue how to achieve this through SQL. Do you know any method to get the new vessel, voyage and leg ?
Thanks.
January 29, 2015 at 10:24 am
I believe CROSS APPLY would be more appropriate to the original requirement ... but I just see that may be a moot point now :-D.
SELECT
TMIS.[BL_ID]
,[POL_NAME]
,[POD_NAME]
,[POR_NAME]
,[LINE]
,[VESSEL]
,[VOYAGE]
,[LEG]
,ca1.[2ND_VESSEL]
,ca1.[2ND_VOYAGE]
,ca1.[2ND_LEG]
,ca1.Transport_Mode_Cd
,ca1.LEG_SEQ_NBR
FROM
tbl_TMIS_DATASET TMIS
CROSS APPLY (
SELECT TOP (1) -- or (2) if you need two rows
ITIN.VESSEL_CD as [2ND_VESSEL]
,ITIN.VOYAGE_CD as [2ND_VOYAGE]
,ITIN.LEG_CD as [2ND_LEG]
,ITIN.Transport_Mode_Cd
,ITIN.LEG_SEQ_NBR
FROM MiniGapp..MG_BL_ITINERARY ITIN
WHERE TMIS.BL_ID = ITIN.BL_ID
ORDER BY ITIN.LEG_SEQ_NBR DESC
) AS ca1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 30, 2015 at 1:58 am
Try this:
CREATE TABLE #tbl_TMIS_DATASET (
[BL_ID] [decimal](10, 0) NOT NULL,
[VESSEL] [nvarchar](10) NULL,
[VOYAGE] [nvarchar](12) NULL,
[LEG] [nchar](3) NULL,
)
INSERT INTO #tbl_TMIS_DATASET
VALUES('19516116','HJAMRC','0010E','E')
CREATE TABLE #MG_BL_ITINERARY(
[BL_ID] [numeric](10, 0) NULL,
[LEG_SEQ_NBR] [numeric](3, 0) NULL,
[VESSEL_CD] [varchar](10) NULL,
[VOYAGE_CD] [varchar](12) NULL,
[LEG_CD] [char](1) NULL,
)
INSERT INTO #MG_BL_ITINERARY
VALUES('19516116','2','HJAMRC','0010E','E')
INSERT INTO #MG_BL_ITINERARY
VALUES('19516116','1','ROBERT','3814S','S')
INSERT INTO #MG_BL_ITINERARY
VALUES('19516116','3','YMUNCN','08E','E')
INSERT INTO #MG_BL_ITINERARY
VALUES('19516116','4','JTWR','002S','S')
SELECT
t.[BL_ID], t.[VESSEL], t.[VOYAGE], t.[LEG],
i.LEG_SEQ_NBR,
x.*
FROM #tbl_TMIS_DATASET t
INNER JOIN #MG_BL_ITINERARY i
ON t.BL_ID = i.BL_ID
AND t.VESSEL = i.VESSEL_CD
AND t.VOYAGE = i.VOYAGE_CD
AND t.LEG = i.LEG_CD
OUTER APPLY (
SELECT VESSEL_CD, VOYAGE_CD, LEG_CD
FROM #MG_BL_ITINERARY i2
WHERE i2.BL_ID = t.BL_ID
AND LEG_SEQ_NBR = i.LEG_SEQ_NBR + 1
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 30, 2015 at 7:34 am
ChrisM@Work (1/30/2015)
Try this:
CREATE TABLE #tbl_TMIS_DATASET ([BL_ID] [decimal](10, 0) NOT NULL,
[VESSEL] [nvarchar](10) NULL,
[VOYAGE] [nvarchar](12) NULL,
[LEG] [nchar](3) NULL,
)
INSERT INTO #tbl_TMIS_DATASET
VALUES('19516116','HJAMRC','0010E','E')
CREATE TABLE #MG_BL_ITINERARY(
[BL_ID] [numeric](10, 0) NULL,
[LEG_SEQ_NBR] [numeric](3, 0) NULL,
[VESSEL_CD] [varchar](10) NULL,
[VOYAGE_CD] [varchar](12) NULL,
[LEG_CD] [char](1) NULL,
)
INSERT INTO #MG_BL_ITINERARY
VALUES('19516116','2','HJAMRC','0010E','E')
INSERT INTO #MG_BL_ITINERARY
VALUES('19516116','1','ROBERT','3814S','S')
INSERT INTO #MG_BL_ITINERARY
VALUES('19516116','3','YMUNCN','08E','E')
INSERT INTO #MG_BL_ITINERARY
VALUES('19516116','4','JTWR','002S','S')
SELECT
t.[BL_ID], t.[VESSEL], t.[VOYAGE], t.[LEG],
i.LEG_SEQ_NBR,
x.*
FROM #tbl_TMIS_DATASET t
INNER JOIN #MG_BL_ITINERARY i
ON t.BL_ID = i.BL_ID
AND t.VESSEL = i.VESSEL_CD
AND t.VOYAGE = i.VOYAGE_CD
AND t.LEG = i.LEG_CD
OUTER APPLY (
SELECT VESSEL_CD, VOYAGE_CD, LEG_CD
FROM #MG_BL_ITINERARY i2
WHERE i2.BL_ID = t.BL_ID
AND LEG_SEQ_NBR = i.LEG_SEQ_NBR + 1
) x
Thanks very much (again !) for your prompt solution. It worked perfectly !! You saved my day once again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply