1

I am stuck in similar situation as this. I have multiple columns with different types of data, and I want to select all columns but group by it with only one column.

My Table:

+--------+----------+----------+-------+-----------------------+
| id     | b_group  | col2     | col3  |       col4            |
+--------+----------+----------+-------+-----------------------+
| 1      | 1        | abcd     | 100   | www.google.com        |
| 2      | 1        | xyz      | 200   | www.yahoo.com         |
| 3      | 2        | dfs      | 200   | www.stackoverflow.com |
| 4      | 3        | asda3    | 78    | www.imdb.com          |
| 5      | 4        | zsdvf4   | 65    | www.youtube.com       |
| 6      | 5        | sdf4     | 101   | www.ymail.com         |
| 7      | 5        | ssdfsd   | 200   | www.gmail.com         |
| 8      | 1        | zxcgdf4  | 200   | www.club.com          |
| 9      | 6        | yujhgj   | 202   | www.thunderbird.com   |
+--------+----------+----------+-------+-----------------------+

After reading the solution provided there, what I understood is to use aggregate function so my query is like:

select MIN(b_group),id,col2,col3,col4 from myTable where col3='200' group by id,col2,col3,col4;

But this is not working in my case, it is giving all the records where col3=200.

My desired Output:

+--------+----------+----------+-------+-----------------------+
| id     | b_group  | col2     | col3  |       col4            |
+--------+----------+----------+-------+-----------------------+
| 2      | 1        | xyz      | 200   | www.yahoo.com         |
| 3      | 2        | dfs      | 200   | www.stackoverflow.com |
| 6      | 5        | sdf4     | 200   | www.ymail.com         |
+--------+----------+----------+-------+-----------------------+

I don't care which record is picked, order don't matter. I just want to select all columns with group by only one.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
nice guy
  • 151
  • 1
  • 14

1 Answers1

2

By applying a group by clause, you get a result row per unique combination of all the columns in it (in this case, per unique combination of id, col2, col3, and col4). Instead, you could use the row_number window function to number rows per b_group, and then select just the (arbitrary) first of each group:

SELECT id, b_group, col2, col3, col4 
FROM   (SELECT id, b_group, col2, col3, col4,
               ROW_NUMBER() OVER (PARTITION BY b_group ORDER BY 1) AS rn
        FROM   mytable                 
        WHERE  col3 = 200)
WHERE  rn = 1
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Thanks a lot, when I ran the above query I got an error **ORA-30485: missing ORDER BY expression** so I used `ROW_NUMBER() OVER (PARTITION BY b_group ORDER BY b_group)` and it worked. – nice guy May 29 '17 at 22:59
  • @niceguy I keep forgetting that `row_number` **requires** an `order by` clause, thanks!. Note that `order by b_group` is meaningless, as all the rows in the same partition will have the same `b_group` anyway. While it's not wrong, it may be a bit confusing to the future reader. In such cases I prefer to dummy it out with something like `order by 1`. – Mureinik May 30 '17 at 08:01