CODEWITHSUNDEEP
sqlvertica
user375868
user375868

Reputation: 1378

Selecting most recent data and grouping by a column

My data in the table look like this:

appName bytes  timestamp
-------- --------
app1   1   10
app2   2   20
app1   3   30
app2   4   40
app1   5   50

I want the following output:

app1   5   50
app2   4   40

i.e. I want the most recent (by timestamp) data with no duplicates.

So far I've following query:

select appName,bytes, max(ts) 
from myTable 
group by appName,bytes

But the above query gives me two rows for app1 and app2. I want only one row for each app and it corresponds to the most recent timestamp.

How should I go about it?

Upvotes: 1

Views: 65

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

Find the Max timestamp per appname and join the result back to table using appname and timestamp. Try this.

SELECT a.appname,
       a.bytes,
       a.timestamp
FROM   yourtable a
       INNER JOIN (SELECT Max(timestamp) timestamp,
                    appname
             FROM   yourtable)B
         ON a.appname = b.appname
            AND a.timestamp = b.timestamp
            AND a.appname = b.appname

or use Analytic Function

SELECT a.appname,
       a.bytes,
       a.timestamp
FROM  (SELECT *,
              Row_number() OVER(partition BY appname
                  ORDER BY timestamp DESC) rn,          
       FROM   yourtable) a
WHERE  rn = 1

Upvotes: 1

Related Questions