none
How to exclude a field from Distinct RRS feed

  • Question

  • This is my SQL

    Select Section,Lineitem,xFundCode From BogeyList
    i want to apply distinct on Section,Lineitem but not on xFundCode. so please guide me how to achieve it ?

    can i do it this way
    select Section,Lineitem,xFundCode, 
    row_number() over (partition by Section,Lineitem order by Section,Lineitem) as Rn From BogeyList
    Where Rn=1
    can i use the above query to fulfill my objective ?

    Thursday, July 18, 2019 9:40 AM

Answers

All replies

  • It is hard to suggest without seeing actual data along with desire result

    You can do 

    SELECT * from 
    (
    select Section,Lineitem,xFundCode, 
    row_number() over (partition by Section,Lineitem order by Section,Lineitem) as Rn From BogeyList

    ) AS Der Where Rn=1 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Sudip_inn Thursday, July 18, 2019 12:36 PM
    Thursday, July 18, 2019 9:46 AM
    Answerer
    • Marked as answer by Sudip_inn Thursday, July 18, 2019 12:38 PM
    Thursday, July 18, 2019 10:20 AM
  • Select Section,Lineitem,xFundCode from (
    select Section,Lineitem,xFundCode, 
    row_number() over (partition by Section,Lineitem order by xFundCode) as Rn 
    From BogeyList
    ) t
    Where Rn=1

    Thursday, July 18, 2019 1:10 PM
    Moderator
  • >> SELECT section_something, lineitem_something, x_fund_code 
    FROM  Bogey_List;

    I want to apply DISTINCT on (section_something, lineitem_something) but not on  x_fund_code; <<

    This request makes no sense because that's not how a select DISTINCT works. Since you didn't have the manners to post DDL, we have no idea what the key is the table Bogey_List

    >> Can I do it this way? <<

    Who knows? You didn't bother to post any output or sample data. Frankly after 30+ years of answering really bad postings, I don't feel like guessing or trying to read your mind. Why don't you follow forum etiquette and post something meaningful?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, July 18, 2019 4:48 PM