Have Geek Brain

Limiting sequential values ​​of one column in sql query

I have a table with the following structure:

id           -int(11)
event_id     -int(11)
photo_id     -int(11)
created_at   -datetime

How to write a query that will return the 100 most recent rows, but ensuring that in photo_id

no more than 4 consecutive lines with the same value
+2


a source to share


5 answers


You can add a where clause that filters out rows for which there are 4 lower rows photo_id

:

select *
from YourTable t1
where 4 > (
    select count(*)
    from YourTable t2
    where t1.event_id = t2.event_id
    and t1.photo_id < t2.photo_id
)
limit 100

This can get slow for huge tables. A faster, but very MySQL-specific option is to use variables. For instance:



select *
from (
    select
        @nr := case 
            when event_id = @event then @nr + 1 
            else 1 
        end as photonr
    ,   @event := event_id
    ,   t1.*
    from YourTable as t1
    cross join (select @event := -1, @nr := 1) as initvars
    order by event_id
) as subquery
where subquery.photonr < 5
limit 100;

Test data used:

drop table if exists YourTable;

create table YourTable (
  id int auto_increment primary key
, event_id int
, photo_id int
);

insert into YourTable (event_id, photo_id)
values (1,1), (1,2), (1,3), (1,4), (1,5), (2,1), (1,6);
+3


a source


I would say that something like this will put you on the right track:

$sql = "SELECT DISTINCT * FROM myTable ORDER BY id ASC LIMIT 100";


In this case, "DISTINCT" will only extract different lines and ignore duplicate lines.

Hope it helps.

0


a source


in oracle, you have to use delay function

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

not sure if this is possible in mySQL.

0


a source


If you are using T-SQL take a look at http://msdn.microsoft.com/en-us/library/ms189798.aspx for ranking functions.

From your question, it looks like NTILE is what you want. Here's my quick attempt at requesting, I'm not in a terminal so it doesn't validate, but you need to start:

SELECT
  id,
  event_id,
  photo_id,
  created_at,
  NTILE(4) OVER (ORDER BY photo_id) AS 'Quartile'
FROM tbl
WHERE NTILE(4) OVER (ORDER BY photo_id)<2
ORDER BY created_at DESC

The linked page has a good example of all the ranking features.

Good luck.

0


a source


Try the following:

SELECT p.id, p.event_id, p.photo_id, p.created_at
FROM photo_table p,
    (

        SELECT photo_id, MAX(created_at) max_date
        FROM photo_table
        GROUP BY photo_id 
    ) t
WHERE p.created_at = t.max_date
        AND p.photo_id = t.photo_id
ORDER BY p.created_at DESC
LIMIT 100

What it does: 1. find the last date the photo was changed 2. find only the latest events of each photo 3. select the first 100 most recent

In PostgreSQL or Oracle, this would be easier with analytics / windows functions like:

FIRST (created_at) OVER (PARTITION BY photo_id ORDER BY created_at DESC)
0


a source






More articles:


All Articles