CODEWITHSUNDEEP
sqlsql-server-2008-r2
RRZ Europe
RRZ Europe

Reputation: 954

sql - select row from group based on multiple values

I have a table like:

| ID    | Val |
+-------+-----+
| abc-1 |  10 |
| abc-2 |  30 |
| cde-1 |  10 |
| cde-2 |  10 |
| efg-1 |  20 |
| efg-2 |  11 |

and would like to get the result based on the substring(ID, 1, 3) and minimum value and ist must be only the first in case the Val has duplicates

| ID    | Val |
+-------+-----+
| abc-1 |  10 |
| cde-1 |  10 |
| efg-2 |  11 |

the problem is that I am stuck, because I cannot use group by substring(id,1,3), ID since it will then have again 2 rows (each for abc-1 and abc-2)

Upvotes: 1

Views: 1124

Answers (2)

Teja
Teja

Reputation: 13534

SELECT SUBSTRING(id,1,3),MIN(val) FROM Table1 GROUP BY SUBSTRING(id,1,3);

You were grouping the columns using both SUBSTRING(id,1,3),id instead of just SUBSTRING(id,1,3). It works perfectly fine.Check the same example in this below link.

http://sqlfiddle.com/#!3/fd9fc/1

Upvotes: 0

MatBailie
MatBailie

Reputation: 86716

WITH
  sorted
AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY substring(id,1,3) ORDER BY val, id) AS sequence_id
  FROM
    yourTable
)
SELECT
  *
FROM
  sorted
WHERE
  sequence_id = 1

Upvotes: 9

Related Questions