MySQL, get the position of a row in a set for multiple row

Question

I have this query that compute a position of a publication inside a set of publications (here named as community), according to there effective_publishing_date :

SELECT p.publication_id
     , p.name publication_name
     , IF(p.scheduled_at is not null, p.scheduled_at, p.created_at) effective_publishing_date
     , @current_rank := @current_rank + 1 publication_rank
FROM publications p
JOIN (SELECT @current_rank := 0) r 
WHERE p.community_id = 8513
ORDER 
    BY effective_publishing_date ASC;

This result as:

[![enter image description here][1]][1]

Now I have a list of feed_item which have each community_id and publication_id as properties and I would like to get, for each feed_item, the associated publication_rank.

For instance, if I have a publication_item with publication_id = 18 and community_id = 2, I want the publication_rank of the publication_id #18 among all the publications of the community_id #2. I don't success to get that in one query (or with sub queries etc).

Thanks per advance,

See https://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql for how to get ranks within groups. You can then join that with your list of `feed_item` to get its rank within its group. — Barmar, Nov 17 '19 at 22:06
Thanks you @Barmar, I solve my problem thanks to your link :-) — christophedebatz, Nov 20 '19 at 22:49

score 1 · Accepted Answer · answered Nov 18 '19 at 00:02

Here are the two solutions I finally found. Thanks to @Barmar !

Only with joins:

SELECT 
  g1.community_id, 
  g1.publication_name, 
  g1.publication_name, 
  g1.publication_id, 
  COUNT(*) AS rank 
FROM 
  (
    SELECT 
      publications.publication_id as publication_id, 
      publications.name as publication_name, 
      publications.community_id as community_id, 
      communities.name as community_name, 
      IF(
        publications.scheduled_at is not null, 
        publications.scheduled_at, publications.created_at
      ) as effective_publishing_date 
    FROM 
      feed_items 
      JOIN publications ON feed_items.publication_id = publications.publication_id 
      JOIN communities ON publications.community_id = communities.community_id 
    WHERE 
      feed_items.user_id = 489387
  ) AS g1 
  JOIN (
    SELECT 
      publications.publication_id as publication_id, 
      publications.community_id as community_id, 
      IF(
        publications.scheduled_at is not null, 
        publications.scheduled_at, publications.created_at
      ) as effective_publishing_date 
    FROM 
      feed_items 
      JOIN publications ON feed_items.publication_id = publications.publication_id 
    WHERE 
      feed_items.user_id = 489387
  ) AS g2 ON (
    g2.effective_publishing_date, g2.publication_id
  ) <= (
    g1.effective_publishing_date, g1.publication_id
  ) 
  AND g1.community_id = g2.community_id 
GROUP BY 
  g1.publication_id, 
  g1.community_id, 
  g1.effective_publishing_date 
ORDER BY 
  g1.community_id, 
  rank ASC;

With SQL variables

SELECT data_table.publication_id, data_table.publication_name, data_table.community_id, data_table.effective_publishing_date,
   @publication := IF(@community <> data_table.community_id, concat(left(@community := data_table.community_id, 0), 0), @publication+1) AS rank
FROM
  (SELECT @publication:= -1) p,
  (SELECT @community:= -1) c,
  (SELECT 
        publication.name as publication_name,
        publication.community_id as community_id,
        feed_item.publication_id as publication_id,
        IF(publication.scheduled_at is not null, publication.scheduled_at, publication.created_at) as effective_publishing_date
   FROM feed_items feed_item
   JOIN publications publication ON feed_item.publication_id = publication.publication_id
   WHERE feed_item.user_id = 489387
   ORDER BY publication.community_id, effective_publishing_date ASC
  ) data_table;

Nice research and adaptation job! – GMB Nov 18 '19 at 00:19 — GMB, Nov 18 '19 at 00:19

MySQL, get the position of a row in a set for multiple row

1 Answers1