'How can I retrieve distinct data by using group by

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 |
+------------------+-------+--------+---------------------+-------+
sqloraclegreatest-n-per-group


Solution 1:[1]

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

Solution 2:[2]

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 
)

Solution 3:[3]

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

Solution 4:[4]

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

Solution 5:[5]

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.

Demo

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 MT0
Solution 3 MT0
Solution 4 debaNik Joardar
Solution 5 Barbaros Özhan

Related Questions