SQL when one column has duplicate rows, then select row where other column is the min value

Question

I have this table

mt.id, mt.otherId, mt.name, mt.myChar, mt.type
1      10            stack      U          "question"  
2      10            stack      D  
3      30            stack      U          "question"
4      10            stack      U          "whydownvotes"

And I want only rows with id 2 and 3 returned (without using the id, otherid as parameter) and ensuring name and type are matching against parameters. And when there is a duplicate otherId = then return the row with min myChar value. So far I have this :

select mt.* from myTable mt
where (mt.myChar = 'U' AND (mt.name = 'stack' AND mt.type LIKE '%question%'))
or (mt.myChar = 'D' and mt.name = 'stack')

So where otherID is 10, I want the row with min char value 'D'. I am going to need a subquery or group using min(myChar) ... ?

How do i remove the first row from the sql fiddle (without using the id):

http://sqlfiddle.com/#!9/c579a/1

edit Jeepers, whats with the downvotes, its clear question isn't it ? There is even a sql fiddle.

`where id in (2, 3)`. I think you should edit your question and describe the logic for choosing those two particular rows. — Gordon Linoff, Apr 02 '15 at 16:03
So you want to select the record with the min value for the `mt.myChar` right. — Mahesh, Apr 02 '15 at 16:07
http://stackoverflow.com/questions/755918/simple-query-to-grab-max-value-for-each-id — NimChimpsky, Apr 07 '15 at 09:29

score 2 · Answer 1 · answered Apr 02 '15 at 17:23

If this is SQL Server, then you can do it in two steps like this:

WITH filtered AS (
  SELECT
    mt.*,
    minType = MIN(mt.type) OVER (PARTITION BY mt.otherId)
  FROM
    dbo.myTable AS mt
  WHERE (mt.myChar = 'U' AND mt.name = 'stack' AND mt.type LIKE '%question%')
     OR (mt.myChar = 'D' AND mt.name = 'stack')
)
SELECT
  id,
  otherId,
  name,
  myChar,
  type
FROM
  filtered
WHERE
  type = minType
;

The filtered subquery is basically your current query but with an additional column that holds minimum type values per otherId. The main query filters the filtered set further based on the type = minType condition.

Mahesh · Answer 2 · 2015-04-03T04:11:04.813

I am assuming you want is a groupwise maximum, one of the most commonly-asked SQL questions You can try as , This query should work on any DBMS. But If you are using the SQL SERVER then you can use the Row_Number() which is very easy to use.Here myTable is your table.

SELECT t0.*
FROM myTable AS t0
LEFT JOIN myTable AS t1 ON t0.otherId = t1.otherId  AND t1.myChar < t0.myChar
WHERE t1.myChar IS NULL;

Here is sql fiddle

SQL when one column has duplicate rows, then select row where other column is the min value

2 Answers2