0

There is a SELECT statement, Output of that could have duplicates and in those duplicates only one row for column k can have have value and other will have 0 value. Need to remove the duplicates have 0 values except one with value

Marli
  • 13
  • 1
  • 1
    Please provide sample data and desired results. Your explanation is quite hard to follow. For instance, it is not clear how many rows you want per `k`. It is not clear if and when you want a row with a value of `0`. – Gordon Linoff Apr 01 '21 at 11:38
  • What is your question here? What have *you* tried? Why didn't it work? Sample data and expected result will help us help you. – Thom A Apr 01 '21 at 11:38
  • Perhaps one of these answer your vague requirement: [sql server select first row from a group](https://stackoverflow.com/q/7344731/2029983), [Get top 1 row of each group](https://stackoverflow.com/q/6841605/2029983), [T-SQL: Deleting all duplicate rows but keeping one](https://stackoverflow.com/q/6025367/2029983), or [How can I remove duplicate rows?](https://stackoverflow.com/q/18932/2029983) – Thom A Apr 01 '21 at 11:40

2 Answers2

1

If you want to filter out 0 values, then you can use:

select t.*
from t
where value = 0;

If you want to keep exactly one row per k, with preference to non-zero values, you can use:

select t.*
from (select t.*,
             row_number() over (partition by k order by value desc) as seqnum
      from t
     ) t
where seqnum = 1;

Note: This assumes that value is never negative. If it can be, then use order by abs(value) desc.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Let's be honest here, answering such a low quality, and unclear, question such as this just promotes such questions from being asked again. Then fact that you even state that the question is unclear in your [comment](https://stackoverflow.com/questions/66903807/need-to-identify-multiple-records-from-the-existing-select-query-output-and-remo#comment118262822_66903807) means you should be voting to close, not answering. Giving an answer tells the OP it's "OK" to post such bad questions. – Thom A Apr 01 '21 at 11:42
  • Thank you Gordon. This solved my requirement – Marli Apr 05 '21 at 10:50
0

We can have ROW_NUMBER() and consider ColumnK value of 0, as rank =1 and filter them out. Assuming there are no negative numbers in ColumnK.

SELECT ColumnA, ColumnB,ColumnC.....
FROM
(SELECT *, row_number() over(partition by columnK ORDER BY columnk asc) as rnk
from tablename
) as t
WHERE rnk > 1 -- 0 will be having rank = 1
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58