从SQL结果中删除重复的行

This is my SQL query to get result from ORACLE DB

SELECT U."UID", 
CONCAT(CONCAT(U.LNAME, ', '), U.FNAME) AS UNAME,
M.NAME,
(SELECT COUNT(LH."UID") FROM DRUPAL_SYS.LOGIN_HISTORY LH WHERE LH."UID"=U."UID" ) AS VIEWS_COUNT,
L.LOGIN as "ACCESS" FROM DRUPAL_SYS.USERS U INNER JOIN DRUPAL_SYS.LOGIN_HISTORY L ON L."UID" = U."UID"
INNER JOIN ABC_WEBAPP.MEMBER_USER_ROLE MUR ON MUR.USER_ID=U.MDES_ID AND MUR.MEMBER_ID LIKE '%' 
INNER JOIN ABC_WEBAPP.MEMBER M ON M.ID=MUR.MEMBER_ID WHERE U.LOGIN != 0 AND U."UID">1 ORDER BY L."LOGIN" ASC`

Its giving a result with duplicate names

But my expected result should be like

SELECT U."UID", 
CONCAT(CONCAT(U.LNAME, ', '), U.FNAME) AS UNAME,
M.NAME,
(SELECT COUNT(LH."UID") FROM DRUPAL_SYS.LOGIN_HISTORY LH WHERE LH."UID"=U."UID" ) AS VIEWS_COUNT,
MAX(L.LOGIN) as "ACCESS" FROM DRUPAL_SYS.USERS U 
INNER JOIN DRUPAL_SYS.LOGIN_HISTORY L ON L."UID" = U."UID"
INNER JOIN ABC_WEBAPP.MEMBER_USER_ROLE MUR ON MUR.USER_ID=U.MDES_ID AND MUR.MEMBER_ID LIKE '%' 
INNER JOIN ABC_WEBAPP.MEMBER M ON M.ID=MUR.MEMBER_ID WHERE U.LOGIN != 0 AND U."UID">1
ORDER BY L."LOGIN" ASC`
GROUP BY UNAME, MNAME

changed L.LOGIN to MAX(L.LOGIN) and added GROUP BY clause

SELECT U."UID", 
CONCAT(CONCAT(U.LNAME, ', '), U.FNAME) AS UNAME,
M.NAME,
(SELECT COUNT(LH."UID") FROM DRUPAL_SYS.LOGIN_HISTORY LH WHERE LH."UID"=U."UID" ) AS VIEWS_COUNT,
L.LOGIN as "ACCESS" FROM DRUPAL_SYS.USERS U INNER JOIN DRUPAL_SYS.LOGIN_HISTORY L ON L."UID" = U."UID"
INNER JOIN ABC_WEBAPP.MEMBER_USER_ROLE MUR ON MUR.USER_ID=U.MDES_ID AND MUR.MEMBER_ID LIKE '%' 
INNER JOIN ABC_WEBAPP.MEMBER M ON M.ID=MUR.MEMBER_ID WHERE U.LOGIN != 0 AND U."UID">1 GROUP BY UNAME ORDER BY L."LOGIN" ASC`

In order to get one result row per UID and UNAME, you'd group by these fields.

GROUP BY UID, UNAME

Then for all remaining fields decide which value to show. E.g. you have four records for "2689 kaila, piyush", which name do you want to show? The first? The last? The maximum? ... Same for views count; the maximum? The sum? And again the same question for the access column.

Example:

select uid, uname, max(name), max(views_count), max(access)
from (<your query here>) query
group by uid, uname;

Copy and paste (ORDER BY clause removed):

select uid, uname, max(name), max(views_count), max(access)
from 
(
  SELECT U."UID", 
  CONCAT(CONCAT(U.LNAME, ', '), U.FNAME) AS UNAME,
  M.NAME,
  (SELECT COUNT(LH."UID") FROM DRUPAL_SYS.LOGIN_HISTORY LH WHERE LH."UID"=U."UID" ) AS VIEWS_COUNT,
  L.LOGIN as "ACCESS" FROM DRUPAL_SYS.USERS U INNER JOIN DRUPAL_SYS.LOGIN_HISTORY L ON L."UID" = U."UID"
  INNER JOIN ABC_WEBAPP.MEMBER_USER_ROLE MUR ON MUR.USER_ID=U.MDES_ID AND MUR.MEMBER_ID LIKE '%' 
  INNER JOIN ABC_WEBAPP.MEMBER M ON M.ID=MUR.MEMBER_ID WHERE U.LOGIN != 0 AND U."UID">1
) query
group by uid, uname;