I have following table and its sample data as follows,
------------------ ------- -------- --------------------- -------
| PhoneCode | SeqID | Active | Token | CUSID |
------------------ ------- -------- --------------------- -------
| e29a5e1c695352b8 | 3898 | I | JYN8CYRVzaKWL-l_K | 50002 |
------------------ ------- -------- --------------------- -------
| e29a5e1c695352b8 | 3900 | I | JYN8CYRVzaKWL-l_K | 50002 |
------------------ ------- -------- --------------------- -------
| 741fb28bc72183e3 | 3899 | I | RU3ReKEw0yin9LxZWCO | 50002 |
------------------ ------- -------- --------------------- -------
| 741fb28bc72183e4 | 3901 | A | RU3ReKEw0yin9LxZWCO | 50002 |
------------------ ------- -------- --------------------- -------
I need to take distinct PhoneCode
with latest SeqID
. So I tried following query.
SELECT UD.PHONECODE, UD.SeqID, UD.ACTIVE, UD.Token
FROM DEVICE UD
WHERE UD.CUSID = '50002' AND UD.ACTIVE = 'I'
GROUP BY PHONECODE
But its makes error. How can I retrieve following output?
------------------ ------- -------- --------------------- -------
| PhoneCode | SeqID | Active | Token | CUSID |
------------------ ------- -------- --------------------- -------
| e29a5e1c695352b8 | 3900 | I | JYN8CYRVzaKWL-l_K | 50002 |
------------------ ------- -------- --------------------- -------
| 741fb28bc72183e3 | 3899 | I | RU3ReKEw0yin9LxZWCO | 50002 |
------------------ ------- -------- --------------------- -------
CodePudding user response:
You can do it without GROUP BY
and without self-joins:
SELECT PHONECODE,
SeqID,
ACTIVE,
Token
FROM (
SELECT d.*,
ROW_NUMBER() OVER (PARTITION BY phonecode ORDER BY seqid DESC) AS rn
FROM device d
WHERE cusid = 50002
AND active = 'I'
)
WHERE rn = 1;
Or, if you want to use GROUP BY
then you can also use KEEP
:
SELECT PHONECODE,
MAX(SeqID) AS seqid,
MAX(ACTIVE) KEEP (DENSE_RANK LAST ORDER BY SeqID) AS active,
MAX(Token) KEEP (DENSE_RANK LAST ORDER BY SeqID) AS token
FROM device
WHERE cusid = 50002
AND active = 'I'
GROUP BY phonecode;
Which, for the sample data:
CREATE TABLE device (PhoneCode, SeqID, Active, Token, CUSID ) AS
SELECT 'e29a5e1c695352b8', 3898, 'I', 'JYN8CYRVzaKWL-l_K', 50002 FROM DUAL UNION ALL
SELECT 'e29a5e1c695352b8', 3900, 'I', 'JYN8CYRVzaKWL-l_K', 50002 FROM DUAL UNION ALL
SELECT '741fb28bc72183e3', 3899, 'I', 'RU3ReKEw0yin9LxZWCO', 50002 FROM DUAL UNION ALL
SELECT '741fb28bc72183e4', 3901, 'A', 'RU3ReKEw0yin9LxZWCO', 50002 FROM DUAL;
Both output:
PHONECODE SEQID ACTIVE TOKEN 741fb28bc72183e3 3899 I RU3ReKEw0yin9LxZWCO e29a5e1c695352b8 3900 I JYN8CYRVzaKWL-l_K
db<>fiddle here
CodePudding user response:
What about a simple sub query
SELECT UD.PHONECODE, UD.SeqID, UD.ACTIVE, UD.Token
FROM device ud
WHERE (UD.PHONECODE, UD.SeqID) IN (
SELECT PHONECODE, MAX(SeqID)
FROM device
WHERE CUSID = '50002' AND ACTIVE = 'I'
GROUP BY PHONECODE
)
CodePudding user response:
You can use MAX()
and a join:
SELECT UD.PHONECODE, UD.ACTIVE, UD.Token, UD.SeqID
FROM DEVICE UD
INNER JOIN (
SELECT PHONECODE, MAX(SeqID) as SEQID
FROM DEVICE
WHERE CUSID = '50002' AND ACTIVE = 'I'
GROUP BY PHONECODE
) T2 ON UD.PHONECODE = T2.PHONECODE AND UD.SEQID = T2.SEQID
CodePudding user response:
The following query will work :
select phonecode,seqid,active,token,cusid
from
(
select * , row_number() over(partition by phonecode order by seqid desc) as rownum
from device
where cusid = 50002 and active = 'I'
) as t1
where rownum = 1
CodePudding user response:
If your database version is 12c
and displaying the returning value of ROW_NUMBER()
function doesn't matter, then such a query as an option without a subquery would be
SELECT d.*,
ROW_NUMBER() OVER (PARTITION BY PhoneCode ORDER BY SeqID DESC) AS rn
FROM device d
WHERE cusid = 50002
AND active = 'I'
ORDER BY rn
FETCH FIRST 1 ROWS WITH TIES
where all ties(equal values) of rn
are included in the result set.