CODEWITHSUNDEEP
phpmysqlsql
Xanger
Xanger

Reputation: 67

SQL query for extract the highest value at a given time range?

I have a problem on a sql query. I need to take the last 30 days and then remove only the 4 results with the "voting" value greater

db structure

Id |    time    | voting
1  | unix time  | 3
2  | unix time  | 2
3  | unix time  | 4
4  | unix time  | 1
5  | unix time  | 6

I would like to take me only the data of: 5-3-1-2

I have tried with

select a.* 
from table a 
inner join 
    ( select votingng, max(time) as latest from table group by voting) v 
        on a.time = v.latest 
        and a.voting = v.voting 
order by time desc limit

Upvotes: 0

Views: 185

Answers (4)

Leo
Leo

Reputation: 908

Try this if you are using teradata

SELECT * from table
qualify row_number () over(order by time desc)=1 ;

or

select * from 
(select table.*, row_number () over(order by time desc) as RANK from table)
where RANK=1

Upvotes: 0

StoneGiant
StoneGiant

Reputation: 1497

It sounds like you are trying to get the top 4 voting results in the past 30 days. Does either of these give you what you want?

SELECT a.*
  FROM table a
 WHERE a.time > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY))
 ORDER BY a.voting DESC
 LIMIT 4;

or

SELECT a.*
  FROM table a
 WHERE DATEDIFF( NOW(), FROM_UNIXTIME(a.time) ) <= 30 
 ORDER BY a.voting DESC
 LIMIT 4;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

I think this is what you want:

select v.*
from voting v
where timestamp >= unix_timestamp(date_sub(curdate(), interval 1 month)
order by voting desc
limit 4;

Upvotes: 1

jonju
jonju

Reputation: 2736

Hope this is what you are looking for:

Select *
From
    Voting
Where
    time Between CURDATE() And DATE_SUB(CURDATE(),INTERVAL 30 DAY)
Order By voting Desc 
Limit 4

Upvotes: 0

Related Questions