CODEWITHSUNDEEP
sqlpostgresql
E. Ron
E. Ron

Reputation: 61

How to select only one set of rows from a group by result table?

I am getting a table like this from my query

     table(name, working, office, hours)
     John T officeA 100
     John F officeA 50
     Bob  T officeA 60
     Bob  F officeA 75
     Carl F officeA 60
     Pete T officeA 80
     Pete F officeA 90

I want for the table to just display the rows where hours for the same person is greater, something like this:

 table(name, working, office, hours)
     John T officeA 100        
     Bob  F officeA 75
     Carl F officeA 60         
     Pete F officeA 90

Also have to show rows where they only have one entry per name so a greater than comparison can't be made as in the case of 'Carl'

My query is like this. Table2 has the hour values that are summed.

SELECT name, working, office, sum(hour) as hours
FROM table1 as t1
INNER JOIN table2 as t2 USING (a)
INNER JOIN table3 as t3 USING (x, y, z)
INNER JOIN table4 as tf USING (b, c)
group by name, office, working

Any advice? thanks.

Upvotes: 1

Views: 511

Answers (2)

Manu Manjunath
Manu Manjunath

Reputation: 364

Are you looking for the row with the highest number of hours for every Name? In that case, I would add a rank function. On your same output, you can run something like -

select name, 
working,
office,
working_hours
rank() over (partition by name order by working_hours desc) as rank_column
from your_output

You can then filter on rank_column = 1 You can also try row_number. Depends on the use case

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use DISTINCT ON:

SELECT DISTINCT ON (name) name, working, office, sum(hour) as hours
FROM table1 t1 JOIN
     table2 t2
     USING (a) JOIN
     table3 t3
     USING (x, y, z) JOIN
     table4 tf 
     USING (b, c)
GROUP BY name, office, working
ORDER BY name, sum(hour) DESC;

DISTINCT ON is a convenient Postgres extension. It returns one row per keys in select. The one row is determined by the ORDER BY.

Upvotes: 2

Related Questions