SQL: Select distinct while ignoring one column

Question

So the query below works really well except for one major flaw, the pr.notes field is text and you can't distinct on text as it is not comparable, is there a way for me to still select it?

SELECT * FROM (

    select distinct d.PhoneNum,d.sourcetable,N.FullName,C.fk_applicationid as ref,t.Subject,t.CreatedDate,pr.notes 
    , RANK() OVER ( PARTITION BY  N.FullName ORDER BY t.CreatedDate DESC ) AS iRank


    from Dial d
    join Database.dbo.DM_PhoneNumbers p on p.PhoneNum1 = d.PhoneNum collate latin1_general_CI_AS
    join Database.dbo.DM_PhoneNumbers on p.PhoneNum2 = d.PhoneNum collate latin1_general_CI_AS
    join Database.dbo.DM_ClientApplicants C on C.FK_ClientID = P.FK_ApplicationID
    join Database.dbo.DM_Names N on c.FK_ClientID = N.FK_ApplicationID
    join Database.dbo.Tasks T on T.FK_ApplicationID = c.FK_ApplicationID
    join database.dbo.dm_projects pr on pr.fk_applicationid = T.fk_applicationid
where c.FK_ClientID in (39157,39160)

) AS t 
WHERE t.iRank = 1

ah well, what is done is done, the way the title is phrased made it hard to search, either way my question is answered. — Anon Ymouse
– Anon Ymouse, Commented Apr 27, 2015 at 16:33

Bogdan Bogdanov · Accepted Answer · 2015-04-27 16:25:12Z

1

Cast pr.notes to VARCHAR(MAX) or NVARCHAR(MAX) depending of data that it holds (I assume that you use SQL Server - it looks that this is the case when I see your code).

    SELECT * FROM (

        select distinct d.PhoneNum,d.sourcetable,N.FullName,C.fk_applicationid as ref,
        t.Subject,t.CreatedDate,CAST(pr.notes AS NVARCHAR(MAX)) AS notes, 
        RANK() OVER ( PARTITION BY  N.FullName ORDER BY t.CreatedDate DESC ) AS iRank

        from Dial d
        join Database.dbo.DM_PhoneNumbers p on p.PhoneNum1 = d.PhoneNum collate latin1_general_CI_AS
        join Database.dbo.DM_PhoneNumbers on p.PhoneNum2 = d.PhoneNum collate latin1_general_CI_AS
        join Database.dbo.DM_ClientApplicants C on C.FK_ClientID = P.FK_ApplicationID
        join Database.dbo.DM_Names N on c.FK_ClientID = N.FK_ApplicationID
        join Database.dbo.Tasks T on T.FK_ApplicationID = c.FK_ApplicationID
        join database.dbo.dm_projects pr on pr.fk_applicationid = T.fk_applicationid
    where c.FK_ClientID in (39157,39160)

    ) AS t 
    WHERE t.iRank = 1;

answered Apr 27, 2015 at 16:25

Bogdan Bogdanov

1,7232 gold badges21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anon Ymouse Over a year ago

as soon as I have the ability too (7 minutes) your getitng the answered on this one :D thank you!

Bogdan Bogdanov Over a year ago

We use app which DB is nightmare - most of the string columns are 'text' datatype. PK are also text fields, so for me was easy to answer such question :). You can also try to change datatype of that column - in our case it works, but you have to test.

Anon Ymouse Over a year ago

tell me about it, the program we work with has so many quirks its unbelievable.

Collectives™ on Stack Overflow

SQL: Select distinct while ignoring one column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related