0

Rank using a left join of another table data.

The app has tokens where the users can vote on. I want to be able to display the rank of each token in the app... The final objective is to create a MySQL view the query, but when trying to create the query I ran into some errors... I am unable to find what is wrong...

The query:

SELECT  a.id,a.name,a.symbol, a.logo, a.market_cap, a.price
      , a.launch_date, a.email, a.description, a.contract_bsc, a.contract_eth
      , a.contract_poly, a.link_website, a.link_telegram, a.link_twitter, a.promoted
      , a.status, a.date, COUNT(b.date) as 'votes'
      , rank() OVER ( partition by a.id order by 'votes' desc ) AS token_rank
FROM `tokens` a 
LEFT join votes b 
  on a.id=b.token_id;

This is the table structure: enter image description here

The error that I am getting: enter image description here

My Ultimate goal is to create a view where I can make search queries from it...

xQbert
  • 34,733
  • 2
  • 41
  • 62
Mohamed Arkib
  • 53
  • 6
  • Normally you can't declare and use an alias in the same select; but this is mySQL.... I'd start by creating a derrived table which contains everything except the analytic function and then add in the analytic based on the aggregegration. In addition; you have no group by for your aggregration; which just isn't wise. IT would help to have sample dat and expected results. to help build from. – xQbert Jul 06 '21 at 20:39

1 Answers1

0

Recommend just keeping it simple:

SELECT a.id,a.name,a.symbol, a.logo  ...
   COUNT(*) as votes
FROM tokens a 
JOIN votes b
  ON a.id=b.token_id
GROUP BY a.id

Don't create this as a VIEW, there's no point. Using VIEWs as the basis for structures normally result in badly performing queries as they need to do unnecessary things.

Notes:

  • Removed LEFT JOIN as I assume you didn't want to put tokens up without any votes. Correct if this isn't the case.
  • COUNT(b.date) did you want to treat a NULL b.date as not being there. * is fine here.

Leave the putting of rank numbers as an application level construction. Decide if you want to count identical votes as an equal number.

danblack
  • 12,130
  • 2
  • 22
  • 41