-1

I have a table similar to below. What I am trying to build is a query that returns distinct vendor concatenated string in order of EID desc. In below example it should return

045-FH;799-HD;67-3M

Eid   Vendor
1      67-3M
2      67-3M
3      67-3M
4      799-HD
5      799-HD
6      045-FH
7      045-FH

This is what SQL query I have but unable to use order by and distinct at same time. Any help is appreciated.

select distinct vendor
from tblVendor
order by Eid
Dale K
  • 25,246
  • 15
  • 42
  • 71
user3038399
  • 91
  • 4
  • 11
  • @SalmanA I don't think that the concatenation was the only goal for the question here, rather the grouping and the ordering as well. Should be reopened with maybe just a reference to the "duplicate" question I think? – squillman Aug 04 '21 at 21:48
  • 1
    @squillman reopened – Salman A Aug 04 '21 at 21:54
  • @user3038399 ignoring the duplicates issue for now, how were you converting the results of this query to a comma separated string? – Dale K Aug 04 '21 at 21:56

1 Answers1

2

You could try something like this. The CTE ensures the list is unique. The ordering of the string aggregation is handled by WITHIN GROUP (ORDER BY Eid DESC).

with unq_cte as (
    select *, row_number() over (partition by Vendor order by Eid desc) rn
    from (values (1, '67-3M'),
                 (2, '67-3M'),
                 (3, '67-3M'),
                 (4, '799-HD'),
                 (5, '799-HD'),
                 (6, '045-FH'),
                 (7, '045-FH')) tblVendor(Eid, Vendor))
select string_agg(Vendor, ',') within group (order by Eid desc)
from unq_cte
where rn=1;

(No column name)
045-FH,799-HD,67-3M

[Edit] Alternately, you could use SELECT TOP 1 WITH TIES to ensure the vendor list is unique

with unq_cte as (
    select top 1 with ties *
    from (values (1, '67-3M'),
                 (2, '67-3M'),
                 (3, '67-3M'),
                 (4, '799-HD'),
                 (5, '799-HD'),
                 (6, '045-FH'),
                 (7, '045-FH')) tblVendor(Eid, Vendor)
     order by row_number() over (partition by Vendor order by Eid desc))
select string_agg(Vendor, ',') within group (order by Eid desc)
from unq_cte;

[Edit 2] Prior to 2017 you could use STUFF and FOR XML to aggregate the string (instead of STRING_AGG)

with unq_cte as (
    select top 1 with ties *
    from (values (1, '67-3M'),
                 (2, '67-3M'),
                 (3, '67-3M'),
                 (4, '799-HD'),
                 (5, '799-HD'),
                 (6, '045-FH'),
                 (7, '045-FH')) tblVendor(Eid, Vendor)
     order by row_number() over (partition by Vendor order by Eid desc))
select stuff((select ' ' + vendor
             from unq_cte
             order by Eid desc
             for xml path('')), 1, 1, '');
SteveC
  • 5,955
  • 2
  • 11
  • 24