The below is an example of what will output when you run the query open: select A.DispatchNote, A.MStockCode, A.NComment from MdnMaster

I'm trying to do a query that'll grab just the first tracking number in the list. and ignore the second (or sometimes third they have)

The database has blank NComment lines when there's an MStockCode, and then the MStockCode lines are blank for every NComment line so I don't know what I'm doing.

What I have so far:

select A.DispatchNote, A.MStockCode, A.NComment
from MdnMaster A
left join MdnDetail B on B.DispatchNote = A.DispatchNote
-- and B.MLineShipDate = getDate() -1 
where A.Customer like 'LAWSON'
  and A.NComment like 'Trk%'
  and A.MStockCode <> ''

My issue is that it gives me nothing since I only know how to ask it explicitly that I want the lines that aren't blank. How do I fix it?

EDIT: I should mention that all of the information comes from the MdnMaster Table (which is A) and MLineShipDate will come from B (MdnDetail). I omitted that information because I didn't think it was pertinent to the question at hand.

An example of what I want to see FROM above:

CodePudding user response：

Here's a quick way to get some results. Hopefully, it will set you on the right path.

I'm assuming you can specify a second column to determine the order of the comments. Replace all instances of Line below with the actual column name.

Select
   m1.DispatchNote, 
   m3.MStockCode,
   m1.NComment
From 
   MdnMaster m1
      Inner Join ( 
         Select DispatchNote, Min(Line) as Line
         From MdnMaster
         Where NComment like 'Trk%'
         Group by DispatchNote ) m2 
            on m1.DispatchNote = m2.DispatchNote and m1.Line = m2.Line
      Inner Join ( 
         Select DispatchNote, Max(MStockCode) as MStockCode
         From MdnMaster
         Group by DispatchNote ) m3 
            on m1.DispatchNote = m3.DispatchNote 

CodePudding user response：

One approach use a cross apply together with select top 1 to retrieve the tracking number.

select M.DispatchNote, M.MStockCode, TRK.NComment
from MdnMaster M
cross apply (
    select top 1 M2.NComment
    from MdnMaster M2
    where M2.DispatchNote = M.DispatchNote
    and M2.NComment LIKE 'Trk# %'
    -- order by ?
) TRK
where M.MStockCode <> ''

Another approach is to join to a subselect that selects all tracking numbers and assigns row numbers withing each group. The final select would limit itself to those tracking numbers where row number = 1.

select M.DispatchNote, M.MStockCode, TRK.NComment
from MdnMaster M
join (
    select M2.DispatchNote, M2.NComment,
        row_number() OVER(PARTITION BY M2.DispatchNote order by (select null)) as RN
    from MdnMaster M2
    where M2.NComment LIKE 'Trk# %'
) TRK ON TRK.DispatchNote = M.DispatchNote
where M.MStockCode <> ''
and TRK.RN = 1

See this db<>fiddle for examples of both.

If there is a chance that there is no tracking number, but you still want to include the other results, change cross apply to outer apply in the first query, or the join to a left join in the second. A cross apply is like an inner join to a subselect, while an outer apply is like a left join.

If you have criteria that prefers one tracking number over another, include it in the order by clause of the subselect in the first query, or replace the order by (select null) placeholder clause in the second. Otherwise, an arbitrary tracking number will be selected.