Jim Mackenzie (2/23/2015)

---

Hi PWalter,

Yes you can create a query based on two different servers, here's some info on how to set it up:

https://msdn.microsoft.com/en-GB/library/ff772782.aspx

Your query would end up looking something like...

GilaMonster (2/16/2015)

---

If you don't want a tested solution, cool, your choice.

Look at Row_Number and the partition by clause, you can then filter on that value to get just the highest....

I just want to find a way to group using 2 columns (without the need...

pwalter83 (2/13/2015)

---

Lowell (2/10/2015)

---

educated guess, since the all those values dates of arrivals are tied to the ship itself, it's not appropriate to arbitrarily grab the max.

you need to use...

Lowell (2/10/2015)

---

educated guess, since the all those values dates of arrivals are tied to the ship itself, it's not appropriate to arbitrarily grab the max.

you need to use row...

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]...

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...

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...

ScottPletcher (12/22/2014)

---

Do you have an underlying "master" table for these codes? If so, add a sort_sequence value to that table and sort based on that. Then you don't...

LutzM (12/22/2014)

---

Just wrap it in a CTE:

; WITH cte AS

(

SELECT DISTINCT EDI_PARTNER_CD

FROM MG_EDI_PARTNER

UNION ALL

SELECT TOP 1 '(NULL)', EDI_PARTNER_CD

FROM ...

g.britton (12/19/2014)

---

Try adding an ORDER BY clause at the end like this:

ORDER By CASE EDI_PARTNER

WHEN 'INTTRA' THEN 1

WHEN 'GTNEXUS' THEN 2

...

WHEN 'EXPEDITORS' THEN 9

ELSE 10

END

, EDI_PARTNER_CD

Idea is, you have a...

Thanks very much,...

David Burrows (11/17/2014)

---

Yes you are correct

the line 'ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN 1 ELSE 2 END ASC) AS [ROWID]'

puts the most desirable name at row 1 ie...

David Burrows (11/14/2014)

---

LEFT JOIN (

SELECT BOOKING_ID,

CASE WHEN COMPANY_ROLE_CD = 'BK' OR BOOKED_BY_FLG = 'Y'

THEN PARTY_NAME

ELSE NULL

END AS [BOOKING PARTY],

ROW_NUMBER() OVER (

PARTITION BY BOOKING_ID

ORDER BY CASE WHEN COMPANY_ROLE_CD =...

David Burrows (11/14/2014)

---

LEFT JOIN (

SELECT BOOKING_ID,

CASE WHEN COMPANY_ROLE_CD = 'BK' OR BOOKED_BY_FLG = 'Y'

THEN PARTY_NAME

ELSE NULL

END AS [BOOKING PARTY],

ROW_NUMBER() OVER (

PARTITION BY BOOKING_ID

ORDER BY CASE WHEN COMPANY_ROLE_CD =...