Manoj Kumar February 2016

SQL to group by two rows?

I need a sql query for the below table

ID    Name      type     priority
1    French     New       2
2    English    New       3
3    Spanish    New       4
4    Deutch     Old       1
5    Japanese   Old       2
6    Chinese    New       5
7    Monotype   Old       3
8    Hindi      New       6
9    Greek      Old       4

I tried ORDER BY type and priority, output returned looks like this:

ID      Name     type     priority
1    French     New       2
2    English    New       3
3    Spanish    New       4
6    Chinese    New       5
8    Hindi      New       6
4    Deutch     Old       1
5    Japanese   Old       2
7    Monotype   Old       3
9    Greek      Old       4

I need an output like

ID      Name     type     priority
1      French    New      2
4      Deutch    Old      1

Can anyone suggest a SQL query for that?

Answers


Ion Sapoval February 2016

select t.* from table t
  inner join
      (select type, priority from table
       group by  type, priority 
       having priority = MIN(priority)) t2
   on t.type = t2.type and t.priority = t2.priority


Gordon Linoff February 2016

The normal way of doing this uses the ANSI standard window function ROW_NUMBER():

select t.*
from (select t.*,
             row_number() over (partition by type order by priority) as seqnum
      from t
     ) t
where seqnum = 1
order by id;


Santiago February 2016

select * from 
(select top 1 * from Table
where type = 'New'
order by priority asc) New
union
select * from 
(select top 1 * from Table
where type = 'Old'
order by priority asc) Old

I thnk, should give you what you're looking for

Post Status

Asked in February 2016
Viewed 2,394 times
Voted 11
Answered 3 times

Search




Leave an answer