SQL Server - SELECT all values in table without having them in the GROUP BY

Question

So this question might have already been answered, but it's a bit difficult to search for or even to ask it.

I want to query a table and filter it in a number of ways. In the example below, I want to figure out who the first employee was for each dept.

Example Table Below Table A

employee_id  |  dept_id  |  start_date  |  name  |  age  | sex
          1  |        1  |    1/5/2000  |   Bill |   25  |  m                 
          2  |        2  |    2/5/2000  |   Biff |   30  |  m                
          3  |        2  |    3/6/2002  |   Gil  |   31  |  m                 
          4  |        2  |    3/7/2002  |   Mar  |   27  |  f                    
          5  |        3  |    1/5/2000  |   Tina |   24  |  f                           
          6  |        3  |    1/5/2000  |   Lin  |   21  |  f

So if I do SELECT dept_id, min(start_date) from Table A GROUP BY dept_id ORDER BY min(start_date)

I'll get

dept_id  |  start_date 
      1  |    1/5/2000                
      2  |    2/5/2000                       
      3  |    1/5/2000

Which is correct information, except I need the other columns. I can't just add them to the SELECT statement because they aren't in the GROUP BY, and I can't just add them to the GROUP BY because then I won't get the correct results.

This comment sort of answered my question, but not fully SQL Select Distinct Values, but order by a different value because I tried doing a subquery inside a join and adding the fields in the SELECT, but it still wanted the fields to be in the GROUP BY.

Please Advise

EDIT: The results I'd like to get are

employee_id |  dept_id  |  start_date  |  name  |  age  | sex
          1 |        1  |    1/5/2000  |   Bill |   25  |  m                 
          2 |        2  |    2/5/2000  |   Biff |   30  |  m                
          5 |        3  |    1/5/2000  |   Tina |   24  |  f

Please be specific on the other fields you want. Right now, you wanted the earliest date a person started by depart id. Clear business rules helps us give you a clear answer! — CRAFTY DBA
– CRAFTY DBA, Commented Jan 25, 2014 at 3:19

Gordon Linoff · Accepted Answer · 2014-01-25 02:00:46Z

2

You want to use row_number():

select employee_id, dept_id, start_date, name, age, sex
from (select a.*, row_number() over (partition by dept_id order by start_date) as seqnum
      from table a
     ) a
where seqnum = 1;

row_number() is a function that assigns sequential numbers to groups of rows. The groups are defined by the partition by clause, so in this case everyone in the same department is in the same group. The numbers are assigned in order based on the order by clause. So, the earliest start date gets a value of 1. The where clause then chooses these records.

answered Jan 25, 2014 at 2:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M.Ali · Accepted Answer · 2014-01-25 02:03:12Z

to find out 1st person for each dept

DECLARE @Table TABLE(employee_id INT,dept_id INT,[start_date] DATE
  ,name VARCHAR(10),age INT, sex CHAR(1))
INSERT INTO @Table VALUES
(1,1,'1/5/2000','Bill',25,'m'),                 
(2,2,'2/5/2000','Biff',30,'m'),                
(3,2,'3/6/2002','Gil',31,'m'),                 
(4,2,'3/7/2002','Mar',27,'f'),                    
(5,3,'1/5/2000','Tina',24,'f'),                           
(6,3,'1/5/2000','Lin',21,'f')

SELECT * FROM
(
  SELECT * 
    , rn = ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY [start_date] ASC) 
  FROM @Table
 )Q
WHERE rn = 1

Result Set

╔═════════════╦═════════╦════════════╦══════╦═════╦═════╦════╗
║ employee_id ║ dept_id ║ start_date ║ name ║ age ║ sex ║ rn ║
╠═════════════╬═════════╬════════════╬══════╬═════╬═════╬════╣
║           1 ║       1 ║ 2000-01-05 ║ Bill ║  25 ║ m   ║  1 ║
║           2 ║       2 ║ 2000-02-05 ║ Biff ║  30 ║ m   ║  1 ║
║           5 ║       3 ║ 2000-01-05 ║ Tina ║  24 ║ f   ║  1 ║
╚═════════════╩═════════╩════════════╩══════╩═════╩═════╩════╝

Collectives™ on Stack Overflow

SQL Server - SELECT all values in table without having them in the GROUP BY

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related