CODEWITHSUNDEEP
mysqlsql-server
300
300

Reputation: 1041

How to select distinct rows from SQL Server table

I am moving existing working queries from MySQL to MS SQL Server (version 11) and I am unable to convert one of the queries that is working fine on MySQL.

It basically selects only distinct rows from the table considering 4 column values.

Table is (with smallest subset of data. Same type of data is present for different name, sub_name and user):

+-------+-------+-------+-------+-----------------+----------+--------+
| col_1 | col_2 | col_3 | col_4 | name            | sub_name | user   |
+-------+-------+-------+-------+-----------------+----------+--------+
|    50 |    40 |    20 |    30 | one_four_eleven | One      | user_1 |
|    50 |    40 |    20 |    30 | one_four_eleven | Two      | user_1 |
|    50 |    40 |    20 |    30 | one_four_eleven | Three    | user_1 |
|    50 |    40 |    20 |    30 | one_four_eleven | Four     | user_1 |
|    50 |    40 |    20 |    30 | one_four_eleven | Five     | user_1 |
|    50 |    40 |    20 |    30 | one_four_eleven | Six      | user_1 |
|    50 |    40 |    20 |    30 | one_four_eleven | Seven    | user_1 |
|    50 |    40 |    20 |    30 | one_four_eleven | Eight    | user_1 |
|    50 |    40 |    20 |    30 | one_four_eleven | Nine     | user_1 |
|    50 |    40 |    15 |    30 | one_four_eleven | Ten      | user_1 |
+-------+-------+-------+-------+-----------------+----------+--------+

And what I need is that if all col_1, col_2, col_3 and col_4 have same value in a row as compared to the other rows, then only one of these identical rows should be selected.

As in the given data, only the last row has different data in col_3 so the result should have only two rows. One row showing identical values in col_1, col_2, col_3, col_4 and name. Second row should be last row.

I am able to do this using MySQL query as shown below and it's result:

mysql> SELECT DISTINCT col_1, col_2, col_3, col_4, name FROM table_name 
WHERE (user = 'user_1' OR user = 'GLOBAL') AND name = 'one_four_eleven' AND sub_name != 'ALL' GROUP BY sub_name;
+-------+-------+-------+-------+-----------------+
| col_1 | col_2 | col_3 | col_4 | name            |
+-------+-------+-------+-------+-----------------+
|    50 |    40 |    20 |    30 | one_four_eleven |
|    50 |    40 |    15 |    30 | one_four_eleven |
+-------+-------+-------+-------+-----------------+

But when I am trying to convert this query to run on MS SQL Server, it gives error: 

Column 'table_name.col_1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I write a query for SQL Server that'll give the same result as above MySQL query.

Upvotes: 0

Views: 2254

Answers (2)

Hogan
Hogan

Reputation: 70538

With the example data given, this will work:

SELECT col_1, col_2, col_3, col_4, name 
FROM table_name 
WHERE (user = 'user_1' OR user = 'GLOBAL') 
  AND name = 'one_four_eleven' 
  AND sub_name != 'ALL' 
GROUP BY col_1, col_2, col_3, col_4, name

or this

SELECT distinct col_1, col_2, col_3, col_4, name 
FROM table_name 
WHERE (user = 'user_1' OR user = 'GLOBAL') 
  AND name = 'one_four_eleven' 
  AND sub_name != 'ALL'

oh, and if you are wondering mysql was basically this:

SELECT max(col_1), max(col_2), max(col_3), max(col_4), max(name) 
FROM table_name 
WHERE (user = 'user_1' OR user = 'GLOBAL') 
  AND name = 'one_four_eleven' 
  AND sub_name != 'ALL' 
GROUP BY sub_name

but that is not as good as the first two.

Upvotes: 1

chancrovsky
chancrovsky

Reputation: 580

I think this can help you:

;WITH cte  AS (
SELECT * 
, ROW_NUMBER() OVER( PARTITION BY col_1, col_2, col_3, col_4, name ORDER BY (SELECT NULL) ) AS rn
FROM yourTable
WHERE (user = 'user_1' OR user = 'GLOBAL') AND name = 'one_four_eleven' AND sub_name != 'ALL'
)

SELECT * FROM cte
WHERE rn = 1

Upvotes: 0

Related Questions