SQL Server: How to Imitate Oracle Keep Dense_Rank Query

SQL Server: how to imitate oracle keep dense_rank query?

I don't think that your particular query will run SQL Server. But you can achieve the same result doing this:

SELECT id, SomeId
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY someId ORDER BY UpdateDate DESC, id DESC) Corr
        FROM MyTable) A
WHERE Corr = 1

SQL Server:keep dense_rank last order by

In SQL Server 2012+, you can use FIRST_VALUE():

So (to keep things simple):

SELECT max(POS_STOP_DATE) keep (dense_rank last order by INV_STR_DATE) as POSITION_STOP_DATE
FROM EMPLOYEE_POSITION
GROUP BY X;

Can be written in SQL Server 2012+ as:

SELECT MAX(POSITION_STOP_DATE)
FROM (SELECT ep.*,
             FIRST_VALUE(POS_STOP_DATE) OVER (PARTITION BY X ORDER BY INV_STR_DATE DESC) as POSITION_STOP_DATE
      FROM EMPLOYEE_POSITION ep
     ) ep
GROUP BY x;

In SQL Server 2005+, you can do this with ROW_NUMBER() and conditional aggregation:

SELECT MAX(CASE WHEN seqnum = 1 THEN POS_STOP_DATE END) as POSITION_STOP_DATE)
FROM (SELECT ep.*,
             ROW_NUMBER() OVER (PARTITION BY X ORDER BY INV_STR_DATE DESC) as seqnum
      FROM EMPLOYEE_POSITION ep
     ) ep
GROUP BY x;

Use of keep dense_rank to find a single value

In your example, if the first priority in the list has multiple age values associated with it, it's the min(age) that determines which one to display. You could swap that out for max(age) if you wanted the highest age displayed, instead of the lowest.

Convert MAX() KEEP (DENSE_RANK LAST ORDER BY TIMESTAMP ) OVER( PARTITION BY) to MySql

As far as concerns, this query gives you the latest record for each (QUESTION_GRP, QUESTION_ID , PROCESS_ID) tuple, ordered by TIMESTAMP.

You could simply use a correlated subquery for that:

CREATE or replace VIEW BPMS_POC_ACC_LATEST_ANSWERS_V 
    (PROCESS_ID, QUESTION_ID, QUESTION_GRP, ANSWER, VERSION, TYPE, AUTHOR, TIMESTAMP, PKEY) AS
SELECT 
    PROCESS_ID, 
    QUESTION_ID, 
    QUESTION_GRP, 
    ANSWER, 
    VERSION, 
    TYPE, 
    AUTHOR, 
    TIMESTAMP, 
    CONCAT(CONCAT(PROCESS_ID,QUESTION_GRP),QUESTION_ID) AS PKEY
FROM BPMS_POC_ACC_ANSWER a
WHERE a.TIMESTAMP = (
    SELECT MAX(a1.TIMESTAMP)
    FROM BPMS_POC_ACC_ANSWER a1
    WHERE 
        a1.QUESTION_GRP = a.QUESTION_GRP
        AND a1.QUESTION_ID = a.QUESTION_ID
        AND a1.PROCESS_ID = a.PROCESS_ID
)

The upside of this approach is that it will work on all versions of MySQL, even in versions 5.x, which do not support window functions.

In MySQL 8.0, you could also use RANK():

CREATE or replace VIEW BPMS_POC_ACC_LATEST_ANSWERS_V 
    (PROCESS_ID, QUESTION_ID, QUESTION_GRP, ANSWER, VERSION, TYPE, AUTHOR, TIMESTAMP, PKEY) AS
SELECT 
    PROCESS_ID, 
    QUESTION_ID, 
    QUESTION_GRP, 
    ANSWER, 
    VERSION, 
    TYPE, 
    AUTHOR, 
    TIMESTAMP, 
    CONCAT(CONCAT(PROCESS_ID,QUESTION_GRP),QUESTION_ID) AS PKEY
FROM (
    SELECT 
        a.*,
        RANK() OVER(PARTITION BY QUESTION_GRP, QUESTION_ID, PROCESS_ID ORDER BY TIMESTAMP DESC) rn
    FROM BPMS_POC_ACC_ANSWER a
) a
WHERE rn = 1

Oracle Statement with DENSE_RANK FIRST ORDER BY in Postgres

SELECT MIN(t2.id) OVER (ORDER BY t2.edit_date) AS id 
FROM temp t2 
GROUP BY t2.sku

---

EDIT:

Even though above did what OP asked, the equivalent for the given query can be:

select min(t.id) id 
from
  (select t.*,
   dense_rank() over (order by t.edit_date) AS rnk 
   from temp t) t
where rnk = 1
group by t.sku;

How to use Having with Group By and Order By in Oracle?

Your problem is getting the row with max date, you have several ways to get it

SELECT ID, status, posted_by, posted_date
FROM MyTable
WHERE (id, posted_date) IN 
    (
        SELECT id, MAX(posted_date)
        FROM MyTable
        GROUP BY id
    )
ORDER BY id;

Using JOIN

SELECT t.ID, t.status, t.posted_by, t.posted_date
FROM MyTable t
INNER JOIN 
(
    SELECT id, MAX(posted_date) AS max_posted_date
    FROM MyTable
    GROUP BY id
) m
ON t.id = m.id AND t.posted_date = m.max_posted_date
ORDER BY t.id;

Or using analytic function

SELECT *
FROM 
(
    SELECT ID, status, posted_by, posted_date,
        ROW_NUMBER() OVER (PARTITION BY id ORDER by posted_date DESC) AS rn
    FROM MyTable
)
WHERE rn = 1 
ORDER BY id;

Oracle keep dense_rank with manual pivoting

This is a bit complicated. The problem is that the keep is looking at all the records. So, I think you can do:

select id,
       min(case when t = 'START'  then d end) keep (dense_rank first 
             order by (case when t = 'START'  then d end) asc)             as start_date,
       max(case when t = 'END'    then d end) keep (dense_rank first 
             order by (case when t = 'END'    then d end) desc nulls last) as end_date,
       max(case when t = 'REPAIR' then d end) keep (dense_rank first 
             order by (case when t = 'REPAIR' then d end) desc nulls last) as repair_date
from example_data
 where t in ('START', 'END', 'REPAIR')
 group by id;

---

Related Topics