How to limit the results of a SQL query by group

Question

I have this query:

select article_number, date, company, size, price 
from price 
order by article_number, company, date

that produces these results:

('50240-20', '2016-03-08 13:06:12.872955', '2B', '20', '645,75')
('50240-20', '2016-03-08 13:13:55.311955', '2B', '20', '645,75')
('50240-20', '2016-03-08 13:47:13.737155', '2B', '20', '645,75')
('50240-20', '2016-03-08 13:51:32.677155', '2B', '20', '645,75')
('50240-20', '2016-03-08 13:06:12.872955', 'Bio', '20', '423,20')
('50240-20', '2016-03-08 13:13:55.311955', 'Bio', '20', '423,20')
('50240-20', '2016-03-08 13:47:13.737155', 'Bio', '20', '423,20')
('50240-20', '2016-03-08 13:51:32.677155', 'Bio', '20', '423,20') ...

I want to limit the results for each article_number and company selection so that it only show 2 results with 2 last different dates. So the result should look like:

('50240-20', '2016-03-08 13:47:13.737155', '2B', '20', '645,75')
('50240-20', '2016-03-08 13:51:32.677155', '2B', '20', '645,75')

('50240-20', '2016-03-08 13:47:13.737155', 'Bio', '20', '423,20')
('50240-20', '2016-03-08 13:51:32.677155', 'Bio', '20', '423,20') ...

Can anyone help me how to do this? A simple limit 2 at the end will only give out 2 results at all and not for each "group"

THX!

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Mar 08 '16 at 13:48
SQLIte: http://stackoverflow.com/questions/751399/sql-query-how-to-apply-limit-within-group-by — Thomas, Mar 08 '16 at 13:51
I created a new question and I would be very happy if you would like to have a look at it. I think it is easy for you and I would be very happy about a solution! http://stackoverflow.com/questions/35870097/how-to-sort-a-table-by-the-difference-in-percent-between-prices-on-different-dat — Flex Texmex, Mar 08 '16 at 14:48

sagi · Accepted Answer · 2016-03-08T13:59:52.423

1

You didn't specify your DBMS so for ORACLE, POSTGRESQL, SQL-SERVER can use ROW_NUMBER() like this:

SELECT * FROM (
    select article_number, date, company, size, price ,
       ROW_NUMBER() OVER(PARTITION BY article_number,company ORDER BY date DESC) as rnk
    from price )
WHERE rnk <= 2
order by article_number, company, date

Solution for MYSQL:

SELECT * FROM (
    select t.article_number, t.date, t.company, t.size, t.price , count(*) as rnk
    from price t
    INNER JOIN price s
    ON(t.article_number = s.article_number and t.company = s.company and t.date <= s.date)
    GROUP BY t.article_number,t.company,t.date,t.size,t.price
)
WHERE rnk <= 2
order by article_number, company, date

EDIT : I see its sqlite, try the second solution for MYSQL, it should work for sqlite as well..

edited Mar 08 '16 at 13:59

answered Mar 08 '16 at 13:51

sagi

40,026
6
59
84

The first one returns error "near (" and the second one just don't give me a result back. But in general it should work like that – Flex Texmex Mar 08 '16 at 13:58
@Eternal_Sunshine Typo, forgot the group by.. try it now – sagi Mar 08 '16 at 14:00
Do you maybe also know how to give out only Article Numbers for a company that changed price? – Flex Texmex Mar 08 '16 at 14:10
Maybe, but it hard to understand what you want like this.. Open a new question and explain it:) @Eternal_Sunshine – sagi Mar 08 '16 at 14:11
I mean just sort all groups by the biggest difference between price of 1 and 2 – Flex Texmex Mar 08 '16 at 14:17
1

I'm sry, its hard to understand.. you seen how much time it took us just to understand each other about the relation.. – sagi Mar 08 '16 at 14:22
I created a new question and I would be very happy if you would like to have a look at it. I think it is easy for you and I would be very happy about a solution! http://stackoverflow.com/questions/35870097/how-to-sort-a-table-by-the-difference-in-percent-between-prices-on-different-dat – Flex Texmex Mar 08 '16 at 14:46

score 0 · Answer 2 · answered Mar 08 '16 at 13:51

0

If Oracle:

Add ROW_NUMBER() OVER (PARTITION BY COLNAME ORDER BY COLNAME ) RN

as column and wrap your query in a SELECT query. In the parent query then use the RN column to limit the results per group.

answered Mar 08 '16 at 13:51

Thomas

213
2
9

I created a new question and I would be very happy if you would like to have a look at it. I think it is easy for you and I would be very happy about a solution! http://stackoverflow.com/questions/35870097/how-to-sort-a-table-by-the-difference-in-percent-between-prices-on-different-dat – Flex Texmex Mar 08 '16 at 14:48

score 0 · Answer 3 · answered Mar 08 '16 at 14:20

0

This is a generic solution which you can use in almost all RDMBS which don't support partition by

select article_number,date, company, size, price from 
(
    select p1.article_number,p1.company,
    count(p1.date) as rno,
    max(p1.date) as date,max(p1.size) as size,max(p1.price) as price
    from price p1 
     inner join price p2
    on p1.article_number=p2.article_number 
     and p1.company=p2.company 
     and p1.date<=p2.date
    group by p1.article_number,p1.company,p1.date
) t1
where t1.rno<=2

answered Mar 08 '16 at 14:20

Utsav

7,914
2
17
38

I created a new question and I would be very happy if you would like to have a look at it. I think it is easy for you and I would be very happy about a solution! http://stackoverflow.com/questions/35870097/how-to-sort-a-table-by-the-difference-in-percent-between-prices-on-different-dat – Flex Texmex Mar 08 '16 at 14:48

How to limit the results of a SQL query by group

3 Answers3