CODEWITHSUNDEEP
sqldatabasesybase
Wvs
Wvs

Reputation: 45

Select data from Sybase database but only select the row with the highest sequence

I'm trying to select data from my database from the highest sequence number, I have been struggling with this for a while and cant get it to work.

The database has a lot of Columns with data. I only want data from the row with the highest sequence number to search in, because the data from lower sequences is not of any value for me. Unfortunately the rows from the lower sequences can not be deleted.

Database looks like this:

-----------------------------
| ID | SEQ | rest of the data
-----------------------------
| 1  |  1  | ..
| 1  |  2  | ....
| 2  |  1  | ..
| 1  |  3  | ....
| 3  |  1  | ..
| 1  |  2  | ....
| 4  |  1  | ........

My question is, how can i select only the ID's with the highest sequence number and search in those rows with the WHERE clause?

Upvotes: 0

Views: 220

Answers (2)

MT0
MT0

Reputation: 168041

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY SEQ DESC ) AS rn
  FROM   your_table t
)
WHERE  rn = 1

or

SELECT ID,
       MAX( seq ) AS seq,
       MAX( other_column_1 ) KEEP ( DENSE_RANK LAST ORDER BY seq ) AS other_column_1,
       MAX( other_column_2 ) KEEP ( DENSE_RANK LAST ORDER BY seq ) AS other_column_2
      -- ...
FROM   your_table
GROUP BY id

or

SELECT *
FROM   your_table t
WHERE  seq IN ( SELECT MAX( seq )
                FROM   your_table x
                WHERE  x.id = t.id )

or

SELECT t.*
FROM   your_table t
       INNER JOIN ( SELECT id, MAX( seq ) AS seq
                    FROM   your_table
                    GROUP BY id ) x
       ON ( x.id = t.id AND x.seq = t.seq )

Upvotes: 0

etsa
etsa

Reputation: 5060

On oracle11g you can use:

SELECT * 
FROM (       
      SELECT YOUR_TABLE.*, RANK() OVER (PARTITION BY ID oRDER BY SEQ DESC) RN 
      FROM YOUR_TABLE) A 
WHERE RN=1;

Upvotes: 2

Related Questions