CODEWITHSUNDEEP
sqlmin
user1523441
user1523441

Reputation: 11

How to use select MIN() in SQL when one field can not be grouped?

I am quite new to SQL so please be gentle. I have tried search for an answer but can not find anything.

I have the following data in a table

Code    Area        Department          Level    Name
HWNET   Highways    Network Services    2        Bob Jones
HWNET   Highways    Network Services    1        Jim Smith
HWTRA   Highways    Traffic Services    2        Andy Johnson
HWTRA   Highways    Traffic Services    1        David Turner

I want to select all the columns from the table but only for the minimum value for each level. My problem is that I need to know the Name but this is stopping me from using the group by function

If I use this it brings back every row in the table

SELECT Code, Area, Department,MIN(Level) Level , Name
FROM TABLE_NAME
GROUP BY Code, Area,Department, Name

This brings back the correct rows but does not contain the Name.

SELECT Code, Area, Department,MIN(Level) Level
FROM TABLE_NAME
GROUP BY Code, Area,Department

Is there a way for me bring back this set of results?

Code    Area        Department          Level    Name
HWNET   Highways    Network Services    1        Jim Smith
HWTRA   Highways    Traffic Services    1        David Turner

Thanks is advance

Upvotes: 1

Views: 23077

Answers (4)

Shann
Shann

Reputation: 690

select   
   s.*   
from table s,  
   (select   
      code, area, department, min(level) m  
    from 
      table  
    group by 
      code,area,department  
) min_v  
where 
   s.code = min_v.code  
   and s.area= min_v.area  
   and s.department = min_v.department  
   and s.level = min_v.m  
;

Upvotes: 0

mucio
mucio

Reputation: 7137

Usually I do it in this ways:

select *
  from TABLE_NAME
 where (Department, 
        Level) in (select Department,
                          min(Level)
                     from TABLE_NAME
                 group by Department)

Here a demo in SQL Fiddle.

Based on your data I used only Department and Level, but if you need to use also Code and Area to identify the right rows just ass them in the WHERE clause and in the SELECT of the subquery.

select *
  from TABLE_NAME
 where (Code,
        Area
        Department, 
        Level) in (select Code,
                          Area,
                          Department,
                          min(Level)
                     from TABLE_NAME
                 group by Code,
                          Area,
                          Department)

Upvotes: 1

Matt Whitfield
Matt Whitfield

Reputation: 6584

You could try something like

SELECT Code, Area, Department, Level, Name
FROM (
  SELECT Code, Area, Department, Level, Name, ROW_NUMBER() OVER (PARTITION BY Code, Area, Department, Name ORDER BY Level) __RN
  FROM TABLE_NAME) subQuery
WHERE __RN = 1

Without knowing more about the schema of the table, it's not easy to say what would be the best way of joining from a grouped version of your data to the table. Using ROW_NUMBER() is likely to be far more efficient, but you might be able to reduce the elements in your partition depending on what is actually the unique key in your table.

Upvotes: 0

juergen d
juergen d

Reputation: 204924

select t.* 
from TABLE_NAME t
inner join
(
  SELECT Code, Area, Department,MIN(Level) mLevel
  FROM TABLE_NAME
  GROUP BY Code, Area,Department
) x on x.Code = t.code 
    and x.Area = t.area 
    and x.Department = t.Department 
    and x.mLevel = t.level

Upvotes: 3

Related Questions