Home » SQL & PL/SQL » SQL & PL/SQL » SQL query to retrieve one record for each employee
| SQL query to retrieve one record for each employee [message #620298] |
Thu, 31 July 2014 01:52  |
 |
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi all,
Help me on writing SQL query to retrieve one record for each employee from the following table? preferably a standard SQL.
CREATE TABLE xxc_contact
(
empnum NUMBER,
alternatecontact VARCHAR2(100),
relation VARCHAR2(10),
phtype VARCHAR2(10),
phone NUMBER
);
insert into xxc_contact values(123456,'Rick Grimes','SP','Cell',9999999999)
insert into xxc_contact values(123456,'Rick Grimes','SP','Work',8888888888)
insert into xxc_contact values(123457,'Daryl Dixon','FR','Work',7777777777)
insert into xxc_contact values(123457,'Daryl Dixon','FR','Home',3333333333)
insert into xxc_contact values(123458,'Maggie Greene','CH','Cell',5555555555)
insert into xxc_contact values(123458,'Maggie Greene','CH','Home',6666666666)
select * from xxc_contact
expected result:
-----------------
EmpNum AlternateContact Relation PhType Phone
123456 Rick Grimes SP Cell 9999999999
123457 Daryl Dixon FR Work 7777777777
123458 Maggie Greene CH Home 6666666666
we are trying with below code using analytical function
SELECT empnum, alternatecontact, relation, phtype, phone
FROM (SELECT a.*, ROW_NUMBER () OVER (PARTITION BY empnum ORDER BY empnum)
r
FROM xxc_contact a)
WHERE r = 1;
Getting output as
please check the attached csv file
-
Attachment: Sheet1.csv
(Size: 0.16KB, Downloaded 971 times)
|
|
|
|
| Re: SQL query to retrieve one record for each employee [message #620299 is a reply to message #620298] |
Thu, 31 July 2014 01:59  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
You were quite close, modified the order by clause from empnum to phone :
SQL> column alternatecontact format A20;
SQL>
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, row_number() over(PARTITION BY empnum ORDER BY phone DESC) r
3 FROM xxc_contact a)
4 WHERE r = 1
/
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Cell 9999999999
123457 Daryl Dixon FR Work 7777777777
123458 Maggie Greene CH Home 6666666666
Regards,
Lalit
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL query to retrieve one record for each employee [message #620306 is a reply to message #620303] |
Thu, 31 July 2014 02:13 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
mist598 wrote on Thu, 31 July 2014 12:34Thanks Lalit...  working fine now
Alternatively, you can also use MAX(phone) instead of row_number().
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, MAX(phone) over(PARTITION BY empnum) rn FROM xxc_contact a)
3 WHERE phone = rn
4 /
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Cell 9999999999
123457 Daryl Dixon FR Work 7777777777
123458 Maggie Greene CH Home 6666666666
[Updated on: Thu, 31 July 2014 02:15] Report message to a moderator |
|
|
|
|
|
| Re: SQL query to retrieve one record for each employee [message #620309 is a reply to message #620308] |
Thu, 31 July 2014 02:22 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
You did not mention the rules for your SQL. So looking at your expected result, based on the pattern of phone I assumed you need the row which has the max phone number.
Using row_number, Oracle assigns ranking based on the descending order of phone withing each group of empnum. And finally selects those rows which has least rank(least since the order is descending for phone).
Using MAX, Oracle assignes the maximum phone to all the rows in each group of empnum. And finally selects the rows having the maximum phone. Order by clause is ommitted here intentionally. You can find out why.
|
|
|
|
| Re: SQL query to retrieve one record for each employee [message #620310 is a reply to message #620305] |
Thu, 31 July 2014 02:34 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
mist598 wrote on Thu, 31 July 2014 09:07Hi Michel,
...
Too late, this must be given with question not with the solution.
Your test case is still invalid: no ";" at the end of insert.
Why there are so much empty lines?
To make you understand what others tell you, all below solutions (only with row_number) are valid answers until you gave the rules (as I asked you):
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, row_number() over(PARTITION BY empnum ORDER BY null) r
3 FROM xxc_contact a)
4 WHERE r = 1
5 /
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Cell 9999999999
123457 Daryl Dixon FR Work 7777777777
123458 Maggie Greene CH Cell 5555555555
3 rows selected.
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, row_number() over(PARTITION BY empnum ORDER BY dbms_random.value) r
3 FROM xxc_contact a)
4 WHERE r = 1
5 /
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Work 8888888888
123457 Daryl Dixon FR Home 3333333333
123458 Maggie Greene CH Home 6666666666
3 rows selected.
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, row_number() over(PARTITION BY empnum ORDER BY alternatecontact) r
3 FROM xxc_contact a)
4 WHERE r = 1
5 /
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Cell 9999999999
123457 Daryl Dixon FR Work 7777777777
123458 Maggie Greene CH Cell 5555555555
3 rows selected.
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, row_number() over(PARTITION BY empnum ORDER BY alternatecontact desc) r
3 FROM xxc_contact a)
4 WHERE r = 1
5 /
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Cell 9999999999
123457 Daryl Dixon FR Work 7777777777
123458 Maggie Greene CH Cell 5555555555
3 rows selected.
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, row_number() over(PARTITION BY empnum ORDER BY relation) r
3 FROM xxc_contact a)
4 WHERE r = 1
5 /
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Cell 9999999999
123457 Daryl Dixon FR Work 7777777777
123458 Maggie Greene CH Cell 5555555555
3 rows selected.
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, row_number() over(PARTITION BY empnum ORDER BY relation desc) r
3 FROM xxc_contact a)
4 WHERE r = 1
5 /
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Cell 9999999999
123457 Daryl Dixon FR Work 7777777777
123458 Maggie Greene CH Cell 5555555555
3 rows selected.
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, row_number() over(PARTITION BY empnum ORDER BY phtype) r
3 FROM xxc_contact a)
4 WHERE r = 1
5 /
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Cell 9999999999
123457 Daryl Dixon FR Home 3333333333
123458 Maggie Greene CH Cell 5555555555
3 rows selected.
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, row_number() over(PARTITION BY empnum ORDER BY phtype desc) r
3 FROM xxc_contact a)
4 WHERE r = 1
5 /
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Work 8888888888
123457 Daryl Dixon FR Work 7777777777
123458 Maggie Greene CH Home 6666666666
3 rows selected.
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, row_number() over(PARTITION BY empnum ORDER BY phone) r
3 FROM xxc_contact a)
4 WHERE r = 1
5 /
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Work 8888888888
123457 Daryl Dixon FR Home 3333333333
123458 Maggie Greene CH Cell 5555555555
3 rows selected.
SQL> SELECT empnum, alternatecontact, relation, phtype, phone
2 FROM (SELECT a.*, row_number() over(PARTITION BY empnum ORDER BY phone desc) r
3 FROM xxc_contact a)
4 WHERE r = 1
5 /
EMPNUM ALTERNATECONTACT RELATION PHTYPE PHONE
---------- -------------------- ---------- ---------- ----------
123456 Rick Grimes SP Cell 9999999999
123457 Daryl Dixon FR Work 7777777777
123458 Maggie Greene CH Home 6666666666
3 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 04:39:20 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
