Sql to select the rows and ignore the duplicate userid in SQL Server

Question

I want to select the rows from my DB and i want to skip when it again repeats like "user2" repeated entries with NULL values.

I tried "SELECT * FROM property_table where text is not NULL and method is not NULL" but i am missing user2 all entries and user4 entry.

This post shows no research or effort on your part. Also, those rows are not duplicates. — dfundako, Feb 15 '18 at 16:44
Possible duplicate of [How can I remove duplicate rows?](https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Josh Withee, Feb 15 '18 at 16:48

score 1 · Answer 1 · answered Feb 15 '18 at 16:44

1

One method is aggregation:

select col1, col2, max(col3), max(col4)
from t
group by col1, col2;

For your sample data it is easier to just filter out NULL values:

select t.*
from t
where col3 is not null;

answered Feb 15 '18 at 16:44

Gordon Linoff

Is there a way i can group by only first 2 columns without aggregate function on rest of columns..the reason is if i use max function i am missing some rows. – Sai Feb 15 '18 at 17:29

score 1 · Answer 2 · answered Feb 15 '18 at 16:47

1

Theoretically NULL and NULL are not duplicate. They cannot be compared.

Ways to remove "logical duplicate"

select *
from Table
where isnull([column3],'') <> isnull([column4],'')

answered Feb 15 '18 at 16:47

SChowdhury

LOL. I like your interpretation of this ambiguous question. I don't approve of `isnull()`, preferring `coalesce()` instead. – Gordon Linoff Feb 15 '18 at 17:03
1

@GordonLinoff I am genuinely interested in why you prefer coalesce over isnull. They are very different beasts and both have some interesting caveats. FWIW, I tend to agree with you on this and use coalesce far more commonly than isnull. – Sean Lange Feb 15 '18 at 17:10
@SeanLange: I guess he chose `coalesce` over the other because: `ISNULL` return value is always considered NOT NULLable whereas `COALESCE` with non-null parameters is considered to be NULL. So the expressions `ISNULL(NULL, 1)` and `COALESCE(NULL, 1)` although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed – SChowdhury Feb 15 '18 at 17:17
@SChowdhury ISNULL can return a null if the second condition IS NULL. The two really aren't equivalent as the datatype returned is determined differently. – Sean Lange Feb 15 '18 at 17:20
*considered* is the keyword! :) – SChowdhury Feb 15 '18 at 17:22

2 Answers2