CODEWITHSUNDEEP
sqlite
amateur_researcher
amateur_researcher

Reputation: 15

SQL query - limit resulting query rows based on non sorted value

I have 2 tables

table 1

per_id | per_name
-------+-----------   
1      | joe
2      | mike
x      | xxxx

and a second table

per_id | job_q
-------+-------    
1      | 500
1      | 250
2      | 125
2      |  10
3      |  54
...

I need a third table where I sum all the job_q and group by per_name and order by job_q asc

table 3

per_name | job_q
---------+---------    
joe      | 750
mike     | 135
...

How can I limit my results up to a specific per_name while keeping my job_q ordering?

Upvotes: 1

Views: 37

Answers (2)

forpas
forpas

Reputation: 164089

You can do it with a CTE:

with cte as (
  select t1.per_name, sum(t2.job_q) job_q
  from table1 t1 inner join table2 t2
  on t2.per_id = t1.per_id
  group by t1.per_name
)
select * from cte
order by job_q desc
limit (
  select count(*) from cte where job_q >= (
    select job_q from cte where per_name = 'mike'
  )
);

See the demo

Upvotes: 0

terrylynch
terrylynch

Reputation: 13572

EDIT - I'm assuming that the OP is talking about qsql which is part of the KDB database (since the q label/tag was used)

Something like this?

q)`job_q xdesc select sum job_q by per_name from (t2 lj 1!t1) where per_name in `joe`mike
per_name| job_q
--------| -----
joe     | 750
mike    | 135

Assumes your tables are in-memory.

Or if you want to filter by ID:

q)`job_q xdesc select sum job_q by per_name from (t2 lj 1!t1) where per_id=2
per_name| job_q
--------| -----
mike    | 135

Upvotes: 1

Related Questions