CODEWITHSUNDEEP
sql
harkesh kumar
harkesh kumar

Reputation: 883

SQL Group By Column With Latest Date

I Have a table of following column name where we have date multiple bin have multiple date with 30-sec date slot I need to pull data based on last entry respect to bin

Suppose 1990025I have 100 entry for today only when I query I need last enter record when I do get find All Entry group by bin am not getting a proper result

For base one Single Bin Query 

select * from battery_data where tcu_date_ist =(
SELECT MAX(tcu_date_ist) FROM battery_data WHERE bin='1990051I');

How i get All Bin Group Query And i also Need latitude, longitude Column in it 

SELECT  latitude, longitude,  bin,created_date
        FROM public.battery_data  
        group by latitude, longitude,  bin  ,created_date
        ORDER BY created_date DESC

Output Am getting like 

12.966  77.5841 "1990025I"  "2019-11-18 12:53:11.984+05:30"
13.0007 77.6128 "1990074I"  "2019-11-18 12:53:11.522+05:30"
12.9567 77.68   "1990088I"  "2019-11-18 12:53:10.663+05:30"
12.9714 77.58   "1990123I"  "2019-11-18 12:53:10.45+05:30"
0       0       "1980020I"  "2019-11-18 12:53:10.151+05:30"
12.966  77.5841 "1990025I"  "2019-11-18 12:53:10.001+05:30"
13.0952 77.5949 "M10299171990132I"  "2019-11-18 12:53:09.922+05:30"
12.9936 77.7478 "1990006I"  "2019-11-18 12:53:08.718+05:30"

Upvotes: 0

Views: 55

Answers (3)

Hemant mehra
Hemant mehra

Reputation: 46

SELECT DISTINCT ON (bin) *
FROM public.battery_data
ORDER BY bin, created_date DESC

Upvotes: 1

Blag
Blag

Reputation: 5894

This no ?

select * from battery_data where tcu_date_ist in(
SELECT MAX(tcu_date_ist) FROM battery_data group by bin);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You need to filter by bin in both the inner and outer query:

select bd.*
from battery_data bd
where bd.bin = '1990051I' and
      bd.tcu_date_ist = (select max(bd2.tcu_date_ist) 
                         from battery_data bd2
                         where bd2.bin = bd.bin
                        );

Or if you want only one row use order by and limit:

select bd.*
from battery_data bd
where bd.bin = '1990051I'
order by bd.tcu_date_ist desc
limit 1;

Upvotes: 0

Related Questions