May 19, 2016 at 9:15 am
Hi simple question:
So:
The combination of {Parcels}.[Dest_Port] and {Parcels}.[Dest_Vessel] can have more then one {BL}.[BLNumber]
I want to catch distinct {Parcels}.[Dest_Port], {Parcels}.[Dest_Vessel]) with the top must BL Number
Port Vessel BLNumber
1 | 1 | BL-123
1 | 1 | BL-123
1 | 1 | BL-123
1 | 1 | BL-123
1 | 1 | BL-1234
1 | 1 | BL-1234
1 | 1 | BL-1234
2 | 1 | BL-1234
2 | 1 | BL-1234
2 | 1 | BL-1234
2 | 1 | BL-123
The query result would be
1 | 1 | BL-123
2 | 1 | BL-1234
It's seems to be easy to do in a query but I'm not seeing how, can you help me?
The folowing code is just one query, the first is Outsystems side, the second you can ignore it but it is the SQL generated to be executed in SQL Server.
SELECT {Parcels}.[Dest_Port], {Parcels}.[Dest_Vessel], {BL}.[BLNumber], count(*) Test
FROM {Parcels}
INNER JOIN {BL} ON {BL}.[Id] = {Parcels}.[BlId]
WHERE {Parcels}.[TransportId] = @TransportId
group by {Parcels}.[Dest_Port], {Parcels}.[Dest_Vessel], {BL}.[BLNumber]
SELECT [OUTSYSTEMS_DEV].DBO.[OSUSR_BTY_PARCELS].[DEST_PORT], [OUTSYSTEMS_DEV].DBO.[OSUSR_BTY_PARCELS].[DEST_VESSEL], [OUTSYSTEMS_DEV].DBO.[OSUSR_BTY_BL].[BLNUMBER], count(*) Test
FROM [OUTSYSTEMS_DEV].DBO.[OSUSR_BTY_PARCELS]
INNER JOIN [OUTSYSTEMS_DEV].DBO.[OSUSR_BTY_BL] ON [OUTSYSTEMS_DEV].DBO.[OSUSR_BTY_BL].[ID] = [OUTSYSTEMS_DEV].DBO.[OSUSR_BTY_PARCELS].[BLID]
WHERE [OUTSYSTEMS_DEV].DBO.[OSUSR_BTY_PARCELS].[TRANSPORTID] = @TransportId
group by [OUTSYSTEMS_DEV].DBO.[OSUSR_BTY_PARCELS].[DEST_PORT], [OUTSYSTEMS_DEV].DBO.[OSUSR_BTY_PARCELS].[DEST_VESSEL], [OUTSYSTEMS_DEV].DBO.[OSUSR_BTY_BL].[BLNUMBER]
May 19, 2016 at 9:35 am
So you want to choose the first BL Number in your list for each combination of Port and Vessel? But how do you decide which is the first? A table has no inherent order, so you need to decide based on a column or set of columns in your table. Please post some table DDL (CREATE TABLE statement) so that we can help you with this.
John
May 19, 2016 at 9:46 am
Hi John thanks for helping
I only want one line per Vessel/Port with the BL Number with more occurrences
Imagine the question: per Vessel/Port what was the BL Number more repeated then we put:
1 | 1 | BL-123
2 | 1 | BL-1234
On my example I only have two combinations Vessel/Port and per combination the BL Number more repeated need to be get on the SQL
I don't have access to the Database, only I'm able to do queries
Imagine all the records are:
Port Vessel BLNumber
1 | 1 | BL-123
1 | 1 | BL-123
1 | 1 | BL-123
1 | 1 | BL-123
1 | 1 | BL-1234
1 | 1 | BL-1234
1 | 1 | BL-1234
2 | 1 | BL-1234
2 | 1 | BL-1234
2 | 1 | BL-1234
2 | 1 | BL-123
May 19, 2016 at 10:46 am
Hi it's resolved I guess...
It was no so simple but I catched this from the internet:
http://www.sql-server-performance.com/2006/find-frequent-values/
And I folow the orientation and here it is, something like this:
SELECT par1.[Dest_Port], par1.[Dest_Vessel], bl1.[BLNumber], count(*) Test
FROM {Parcels} par1
INNER JOIN {BL} bl1 ON bl1.[Id] = par1.[BlId]
group by par1.[Dest_Port], par1.[Dest_Vessel], bl1.[BLNumber]
having count(*) =
(SELECT MAX(Test)
FROM
(SELECT par2.[Dest_Port], par2.[Dest_Vessel], bl2.[BLNumber], count(*) Test
FROM {Parcels} par2
INNER JOIN {BL} bl2 ON bl2.[Id] = par2.[BlId]
WHERE par1.[Dest_Port] = par2.[Dest_Port]
AND par1.[Dest_Vessel] = par2.[Dest_Vessel]
group by par2.[Dest_Port], par2.[Dest_Vessel], bl2.[BLNumber])c)
Thank you
May 19, 2016 at 11:41 am
A different option. I included sample data generation, in case someone else want s to try something. It should have been provided by you in this way to prevent time loss on creating it.
DECLARE @Sample TABLE(
Port int,
Vessel int,
BLNumber varchar(10))
INSERT INTO @Sample
VALUES
(1, 1, 'BL-123 '),
(1, 1, 'BL-123 '),
(1, 1, 'BL-123 '),
(1, 1, 'BL-123 '),
(1, 1, 'BL-1234'),
(1, 1, 'BL-1234'),
(1, 1, 'BL-1234'),
(2, 1, 'BL-1234'),
(2, 1, 'BL-1234'),
(2, 1, 'BL-1234'),
(2, 1, 'BL-123 ');
WITH cteCount AS(
SELECT Port, Vessel, BLNumber, COUNT(*) BLCount
FROM @Sample
GROUP BY Port, Vessel, BLNumber
),
cteRows AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY Port, Vessel ORDER BY BLCount DESC) rn
FROM cteCount
)
SELECT Port, Vessel, BLNumber
FROM cteRows
WHERE rn = 1;
May 19, 2016 at 12:56 pm
You can do this with only one CTE.
;
WITH cteCount AS(
SELECT Port, Vessel, BLNumber, ROW_NUMBER() OVER( PARTITION BY Port, Vessel ORDER BY COUNT(*) DESC ) rn
FROM @Sample
GROUP BY Port, Vessel, BLNumber
)
SELECT Port, Vessel, BLNumber
FROM cteCount
WHERE rn = 1
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply