CODEWITHSUNDEEP
sqlsql-server
otk
otk

Reputation: 411

sql - Delete/omit rn column within query result, using ROW_NUMBER function

i have several unique IDs, these unique ID's have repeating data based on

timestamp

.

I get the latest data by timestamp for each unique ID in the following way:

SELECT * 
FROM (SELECT uniqueID, timestamp
                      ROW_NUMBER() OVER(PARTITION BY uniqueID ORDER BY timestamp DESC) rn 
      FROM table_name
     ) a 
WHERE rn = 1

This works, but the query also returns a rn column. So my result set looks like this:

uniqueID timestamp rn
1        15:00     1
2        15:00     1

How can i omit the column rn from the query-reults? To make it look like this:

uniqueID timestamp
1        15:00
2        15:00

Ref: http://www.postgresqltutorial.com/postgresql-row_number/

Upvotes: 2

Views: 393

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

If you have a lot of columns, you might find it simpler to do:

SELECT a.*
FROM table_name a 
WHERE a.timestamp = (SELECT MAX(a2.timestamp) FROM table_name a2 WHERE a2.uniqueid = a.uniqueid);

In addition, this probably has slightly better performance if you have an index on (uniqueid, timestamp).

Ironically, Postgres has a simpler solution (and you are referring to a Postgres tutorial):

select distinct on (uniqueid) a.*
from table_name a
order by uniqueid, timestamp desc;

Upvotes: 0

PSK
PSK

Reputation: 17943

You can change your query like following. You don't need to put *, just mention the column you need.

SELECT a.uniqueid , 
       a.timestamp 
FROM   ( 
        SELECT   uniqueid, 
                    timestamp, row_number() OVER(partition BY uniqueid ORDER BY timestamp DESC) rn
        FROM     table_name) a 
WHERE  rn = 1

Upvotes: 2

Related Questions