CODEWITHSUNDEEP
sqlhivehiveql
akshay dirisala
akshay dirisala

Reputation: 41

Select latest 30 dates for each unique ID

This is a sample data file dateinfo

Data Contains unique IDs with different latitudes and longitudes on multiple timestamps.I would like to select the rows of latest 30 days of coordinates for each unique ID.Please help me on how to run the query .This date is in Hive table

Regards, Akshay

Upvotes: 0

Views: 683

Answers (3)

Olga Romantsova
Olga Romantsova

Reputation: 1081

According to your example above (where no current year dates for id=2,3), you can numbering date for each id (order by date descending) using window function ROW_NUMBER(). Then just get latest 30 values:

--get all values for each id where num<=30 (get last 30 days for each day)
select * from
(
--numbering each date for each id order by descending
  select *, row_number()over(partition by ID order by DATE desc)num from Table
)X
where num<=30

If you need to get only unique dates (without consider time) for each id, then can try this query:

select * from
(
 --numbering date for each id
 select *, row_number()over(partition by ID order by new_date desc)num
 from
  (
   -- move duplicate using distinct
   select  distinct ID,cast(DATE as date)new_date  from Table
  )X
)Y
where num<=30

Upvotes: 1

SteveC
SteveC

Reputation: 6015

select * from MyTable 
where
  [Date]>=dateadd(d, -30, getdate());

To group by ID and perform aggregation, something like this

select ID, 
       count(*) row_count,
       max(Latitude) max_lat,
       max(Longitude) max_long
from MyTable 
where
  [Date]>=dateadd(d, -30, getdate())
group by ID;

Upvotes: 0

Atif
Atif

Reputation: 2210

In Oracle this will be:

SELECT * FROM TEST_DATE1
WHERE DATEUPDT > SYSDATE - 30;

Upvotes: 0

Related Questions