SQL - Get records with highest value in column then pick the most recent of those records-CodePudding

I have been looking at solutions here and have been rewriting my code but I'm still having trouble getting it to work. I'd appreciate all and any help. Please bear in mind SQL is new to me!

I want to write a sql query that gets the record with the most likes (per likes column) and if there are multiple records with the same max likes then I only want the most recent record (per date column). Using the below table as an example, you can see I want row #4 because not only does it have max(Likes) but it's also more recent than row #1.

I tried the below but it returns nothing because it's trying to get a record that satisfies being both the newest and most liked in the table.

SELECT *
FROM table
WHERE Likes = (SELECT MAX(Likes) FROM perks)
  AND Date =  (SELECT MAX(Date)  FROM table)

I also tried lots of versions of the below code that I found on here. I tried to update it for my own situation but I couldn't find any explanation from my searches as to why "t." is being placed in front of the column names. I'm just plain confused by the whole inner join part in general and it's bringing up weird results or just errors whatever way I try it.

   select t.Id,t.Uid,t.Upic,t.Text,max(t.Date),t.Likes
    from table t
    inner join (
        select Id,Uid,Upic,Text,Date, max(Likes) as MaxLike
        from table
        group by Likes
    ) tm on max(t.Date)=tm.Date and t.Likes = tm.MaxLike

CodePudding user response：

You can use TOP 1 or LIMIT 1 (depending on DBMS) with ORDER BY. Based on your sample data, something like this should work.

SELECT
    *
FROM t1
INNER JOIN (
    SELECT TOP 1
      MAX(likes) as likes
      ,date
    FROM t1
    group by date
    order by date desc
)  as s on t1.date = s.date and t1.likes = s.likes

What if there's the same number of likes on the same date? You'll still return multiple rows.

CodePudding user response：

TruthTeller.

You may combine using ORDER BY clause and limiting the result set to 1.

SELECT TOP 1 *
FROM Table
ORDER BY LIKES DESC
        ,DATE DESC;

For reference: https://www.w3schools.com/sql/sql_top.asp

CodePudding user response：

Would something like the below query solve your issue?

gets the record with the most likes [...] I only want the most recent record

By running a top (or limit) with an order by, you can specify to only return a set number of records based on the ordering. Ordering by likes desc means that the result set is first ordered by the number of likes. Then applying the order by date desc would mean the most recent value is at the top. SELECT TOP 1 would then only return the top row. If you have more than one record at the same time, with the same number of likes, it will still only return one row.

select top 1
    *
from 
    table
order by
    likes desc
    ,date desc;

select
    *
from 
    table
order by
    likes desc
    ,date desc 
limit 1;

In reference to your second question of:

why "t." is being placed in front of the column names

In your example query, you alias the table table with the name t, and the sub-query is aliased tm. Without the t before each column name, your RDBMS will most likely tell you that it doesn't know which column id you wish to have displayed - id from table or id from the sub-query tm.