MAX() KEEP (DENSE_RANK LAST ORDER BY ) OVER() PARTITION BY()

View All Scripts Login to Run Script

Script Name MAX() KEEP (DENSE_RANK LAST ORDER BY ) OVER() PARTITION BY()
Description MAX() KEEP (DENSE_RANK LAST ORDER BY DESC NULLS LAST) OVER() PARTITION BY() "Highest Value"
Area SQL Analytics
Contributor Krishnaraja
Created Thursday October 19, 2017

Statement 1

select * from hr.employees order by HIRE_DATE desc nulls last


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-APR-08 SA_REP 6100 .1 148 80
167 Amit Banda ABANDA 011.44.1346.729268 21-APR-08 SA_REP 6200 .1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-MAR-08 SA_REP 6400 .1 147 80
128 Steven Markle SMARKLE 650.124.1434 08-MAR-08 ST_CLERK 2200  - 120 50
165 David Lee DLEE 011.44.1346.529268 23-FEB-08 SA_REP 6800 .1 147 80
136 Hazel Philtanker HPHILTAN 650.127.1634 06-FEB-08 ST_CLERK 2200  - 122 50
183 Girard Geoni GGEONI 650.507.9879 03-FEB-08 SH_CLERK 2800  - 120 50
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-08 SA_MAN 10500 .2 100 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-JAN-08 SA_REP 7200 .1 147 80
199 Douglas Grant DGRANT 650.507.9844 13-JAN-08 SH_CLERK 2600  - 124 50
179 Charles Johnson CJOHNSON 011.44.1644.429262 04-JAN-08 SA_REP 6200 .1 149 80
191 Randall Perkins RPERKINS 650.505.4876 19-DEC-07 SH_CLERK 2500  - 122 50
135 Ki Gee KGEE 650.127.1734 12-DEC-07 ST_CLERK 2400  - 122 50
113 Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT 6900  - 108 100
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-NOV-07 SA_REP 7000 .15 145 80
124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-07 ST_MAN 5800  - 100 50
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-OCT-07 SA_MAN 11000 .3 100 80
119 Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK 2500  - 114 30
198 Donald OConnell DOCONNEL 650.507.9833 21-JUN-07 SH_CLERK 2600  - 124 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-JUN-07 SH_CLERK 2500  - 120 50
178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-07 SA_REP 7000 .15 149  - 
104 Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG 6000  - 103 60
132 TJ Olson TJOLSON 650.124.8234 10-APR-07 ST_CLERK 2100  - 121 50
172 Elizabeth Bates EBATES 011.44.1343.529268 24-MAR-07 SA_REP 7300 .15 148 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-MAR-07 SA_REP 9500 .15 147 80
195 Vance Jones VJONES 650.501.4876 17-MAR-07 SH_CLERK 2800  - 123 50
171 William Smith WSMITH 011.44.1343.629268 23-FEB-07 SA_REP 7400 .15 148 80
187 Anthony Cabrio ACABRIO 650.509.4876 07-FEB-07 SH_CLERK 3000  - 121 50
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG 4200  - 103 60
127 James Landry JLANDRY 650.124.1334 14-JAN-07 ST_CLERK 2400  - 120 50
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 09-DEC-06 SA_REP 7500 .2 145 80
118 Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK 2600  - 114 30
161 Sarath Sewall SSEWALL 011.44.1345.529268 03-NOV-06 SA_REP 7000 .25 146 80
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-SEP-06 ST_CLERK 2700  - 120 50
134 Michael Rogers MROGERS 650.127.1834 26-AUG-06 ST_CLERK 2900  - 122 50
190 Timothy Gates TGATES 650.505.3876 11-JUL-06 SH_CLERK 2900  - 122 50
144 Peter Vargas PVARGAS 650.121.2004 09-JUL-06 ST_CLERK 2500  - 124 50
194 Samuel McCain SMCCAIN 650.501.3876 01-JUL-06 SH_CLERK 3200  - 123 50
186 Julia Dellinger JDELLING 650.509.3876 24-JUN-06 SH_CLERK 3400  - 121 50
197 Kevin Feeney KFEENEY 650.507.9822 23-MAY-06 SH_CLERK 3000  - 124 50
196 Alana Walsh AWALSH 650.507.9811 24-APR-06 SH_CLERK 3100  - 124 50
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-APR-06 SA_REP 8400 .2 149 80
140 Joshua Patel JPATEL 650.121.1834 06-APR-06 ST_CLERK 2500  - 123 50
153 Christopher Olsen COLSEN 011.44.1344.498718 30-MAR-06 SA_REP 8000 .2 145 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-06 SA_REP 8600 .2 149 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-MAR-06 SA_REP 10000 .2 148 80
143 Randall Matos RMATOS 650.121.2874 15-MAR-06 ST_CLERK 2600  - 124 50
112 Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT 7800  - 108 100
181 Jean Fleaur JFLEAUR 650.507.9877 23-FEB-06 SH_CLERK 3100  - 120 50
139 John Seo JSEO 650.121.2019 12-FEB-06 ST_CLERK 2700  - 123 50
106 Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 IT_PROG 4800  - 103 60
170 Tayler Fox TFOX 011.44.1343.729268 24-JAN-06 SA_REP 9600 .2 148 80
180 Winston Taylor WTAYLOR 650.507.9876 24-JAN-06 SH_CLERK 3200  - 120 50
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 IT_PROG 9000  - 102 60
116 Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK 2900  - 114 30
160 Louise Doran LDORAN 011.44.1345.629268 15-DEC-05 SA_REP 7500 .3 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-NOV-05 SA_REP 10500 .25 147 80
130 Mozhe Atkinson MATKINSO 650.124.6234 30-OCT-05 ST_CLERK 2800  - 121 50
138 Stephen Stiles SSTILES 650.121.2034 26-OCT-05 ST_CLERK 3200  - 123 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-OCT-05 ST_MAN 6500  - 100 50
111 Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700  - 108 100
110 John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200  - 108 100
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 AD_VP 17000  - 100 90
129 Laura Bissot LBISSOT 650.124.5234 20-AUG-05 ST_CLERK 3300  - 121 50
152 Peter Hall PHALL 011.44.1344.478968 20-AUG-05 SA_REP 9000 .25 145 80
202 Pat Fay PFAY 603.123.6666 17-AUG-05 MK_REP 6000  - 201 20
189 Jennifer Dilly JDILLY 650.505.2876 13-AUG-05 SH_CLERK 3600  - 122 50
117 Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK 2800  - 114 30
125 Julia Nayer JNAYER 650.124.1214 16-JUL-05 ST_CLERK 3200  - 120 50
105 David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG 4800  - 103 60
188 Kelly Chung KCHUNG 650.505.1876 14-JUN-05 SH_CLERK 3800  - 122 50
121 Adam Fripp AFRIPP 650.123.2234 10-APR-05 ST_MAN 8200  - 100 50
151 David Bernstein DBERNSTE 011.44.1344.345268 24-MAR-05 SA_REP 9500 .25 145 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-MAR-05 SA_REP 8800 .25 149 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-MAR-05 SA_REP 11500 .25 148 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-MAR-05 SA_REP 8000 .3 146 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR-05 SA_MAN 12000 .3 100 80
193 Britney Everett BEVERETT 650.501.2876 03-MAR-05 SH_CLERK 3900  - 123 50
185 Alexis Bull ABULL 650.509.2876 20-FEB-05 SH_CLERK 4100  - 121 50
131 James Marlow JAMRLOW 650.124.7234 16-FEB-05 ST_CLERK 2500  - 121 50
150 Peter Tucker PTUCKER 011.44.1344.129268 30-JAN-05 SA_REP 10000 .3 145 80
142 Curtis Davies CDAVIES 650.121.2994 29-JAN-05 ST_CLERK 3100  - 124 50
146 Karen Partners KPARTNER 011.44.1344.467268 05-JAN-05 SA_MAN 13500 .3 100 80
145 John Russell JRUSSEL 011.44.1344.429268 01-OCT-04 SA_MAN 14000 .4 100 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 01-AUG-04 SA_REP 9000 .35 146 80
120 Matthew Weiss MWEISS 650.123.1234 18-JUL-04 ST_MAN 8000  - 100 50
133 Jason Mallin JMALLIN 650.127.1934 14-JUN-04 ST_CLERK 3300  - 122 50
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-04 SA_REP 11000 .3 149 80
157 Patrick Sully PSULLY 011.44.1345.929268 04-MAR-04 SA_REP 9500 .35 146 80
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-04 MK_MAN 13000  - 100 20
192 Sarah Bell SBELL 650.501.1876 04-FEB-04 SH_CLERK 4000  - 123 50
156 Janette King JKING 011.44.1345.429268 30-JAN-04 SA_REP 10000 .35 146 80
184 Nandita Sarchand NSARCHAN 650.509.1876 27-JAN-04 SH_CLERK 4200  - 121 50
141 Trenna Rajs TRAJS 650.121.8009 17-OCT-03 ST_CLERK 3500  - 124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-03 AD_ASST 4400  - 101 10
137 Renske Ladwig RLADWIG 650.121.1234 14-JUL-03 ST_CLERK 3600  - 123 50
100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 24000  -  - 90
115 Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK 3100  - 114 30
122 Payam Kaufling PKAUFLIN 650.123.3234 01-MAY-03 ST_MAN 7900  - 100 50
114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 11000  - 100 30
108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 12008  - 101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000  - 108 100
206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 8300  - 205 110
203 Susan Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP 6500  - 101 40
204 Hermann Baer HBAER 515.123.8888 07-JUN-02 PR_REP 10000  - 101 70
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 12008  - 101 110
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 AD_VP 17000  - 100 90

107 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
173	Sundita	Kumar	SKUMAR	011.44.1343.329268	21-APR-08	SA_REP	6100	.1	148	80
167	Amit	Banda	ABANDA	011.44.1346.729268	21-APR-08	SA_REP	6200	.1	147	80
166	Sundar	Ande	SANDE	011.44.1346.629268	24-MAR-08	SA_REP	6400	.1	147	80
128	Steven	Markle	SMARKLE	650.124.1434	08-MAR-08	ST_CLERK	2200	-	120	50
165	David	Lee	DLEE	011.44.1346.529268	23-FEB-08	SA_REP	6800	.1	147	80
136	Hazel	Philtanker	HPHILTAN	650.127.1634	06-FEB-08	ST_CLERK	2200	-	122	50
183	Girard	Geoni	GGEONI	650.507.9879	03-FEB-08	SH_CLERK	2800	-	120	50
149	Eleni	Zlotkey	EZLOTKEY	011.44.1344.429018	29-JAN-08	SA_MAN	10500	.2	100	80
164	Mattea	Marvins	MMARVINS	011.44.1346.329268	24-JAN-08	SA_REP	7200	.1	147	80
199	Douglas	Grant	DGRANT	650.507.9844	13-JAN-08	SH_CLERK	2600	-	124	50
179	Charles	Johnson	CJOHNSON	011.44.1644.429262	04-JAN-08	SA_REP	6200	.1	149	80
191	Randall	Perkins	RPERKINS	650.505.4876	19-DEC-07	SH_CLERK	2500	-	122	50
135	Ki	Gee	KGEE	650.127.1734	12-DEC-07	ST_CLERK	2400	-	122	50
113	Luis	Popp	LPOPP	515.124.4567	07-DEC-07	FI_ACCOUNT	6900	-	108	100
155	Oliver	Tuvault	OTUVAULT	011.44.1344.486508	23-NOV-07	SA_REP	7000	.15	145	80
124	Kevin	Mourgos	KMOURGOS	650.123.5234	16-NOV-07	ST_MAN	5800	-	100	50
148	Gerald	Cambrault	GCAMBRAU	011.44.1344.619268	15-OCT-07	SA_MAN	11000	.3	100	80
119	Karen	Colmenares	KCOLMENA	515.127.4566	10-AUG-07	PU_CLERK	2500	-	114	30
198	Donald	OConnell	DOCONNEL	650.507.9833	21-JUN-07	SH_CLERK	2600	-	124	50
182	Martha	Sullivan	MSULLIVA	650.507.9878	21-JUN-07	SH_CLERK	2500	-	120	50
178	Kimberely	Grant	KGRANT	011.44.1644.429263	24-MAY-07	SA_REP	7000	.15	149	-
104	Bruce	Ernst	BERNST	590.423.4568	21-MAY-07	IT_PROG	6000	-	103	60
132	TJ	Olson	TJOLSON	650.124.8234	10-APR-07	ST_CLERK	2100	-	121	50
172	Elizabeth	Bates	EBATES	011.44.1343.529268	24-MAR-07	SA_REP	7300	.15	148	80
163	Danielle	Greene	DGREENE	011.44.1346.229268	19-MAR-07	SA_REP	9500	.15	147	80
195	Vance	Jones	VJONES	650.501.4876	17-MAR-07	SH_CLERK	2800	-	123	50
171	William	Smith	WSMITH	011.44.1343.629268	23-FEB-07	SA_REP	7400	.15	148	80
187	Anthony	Cabrio	ACABRIO	650.509.4876	07-FEB-07	SH_CLERK	3000	-	121	50
107	Diana	Lorentz	DLORENTZ	590.423.5567	07-FEB-07	IT_PROG	4200	-	103	60
127	James	Landry	JLANDRY	650.124.1334	14-JAN-07	ST_CLERK	2400	-	120	50
154	Nanette	Cambrault	NCAMBRAU	011.44.1344.987668	09-DEC-06	SA_REP	7500	.2	145	80
118	Guy	Himuro	GHIMURO	515.127.4565	15-NOV-06	PU_CLERK	2600	-	114	30
161	Sarath	Sewall	SSEWALL	011.44.1345.529268	03-NOV-06	SA_REP	7000	.25	146	80
126	Irene	Mikkilineni	IMIKKILI	650.124.1224	28-SEP-06	ST_CLERK	2700	-	120	50
134	Michael	Rogers	MROGERS	650.127.1834	26-AUG-06	ST_CLERK	2900	-	122	50
190	Timothy	Gates	TGATES	650.505.3876	11-JUL-06	SH_CLERK	2900	-	122	50
144	Peter	Vargas	PVARGAS	650.121.2004	09-JUL-06	ST_CLERK	2500	-	124	50
194	Samuel	McCain	SMCCAIN	650.501.3876	01-JUL-06	SH_CLERK	3200	-	123	50
186	Julia	Dellinger	JDELLING	650.509.3876	24-JUN-06	SH_CLERK	3400	-	121	50
197	Kevin	Feeney	KFEENEY	650.507.9822	23-MAY-06	SH_CLERK	3000	-	124	50
196	Alana	Walsh	AWALSH	650.507.9811	24-APR-06	SH_CLERK	3100	-	124	50
177	Jack	Livingston	JLIVINGS	011.44.1644.429264	23-APR-06	SA_REP	8400	.2	149	80
140	Joshua	Patel	JPATEL	650.121.1834	06-APR-06	ST_CLERK	2500	-	123	50
153	Christopher	Olsen	COLSEN	011.44.1344.498718	30-MAR-06	SA_REP	8000	.2	145	80
176	Jonathon	Taylor	JTAYLOR	011.44.1644.429265	24-MAR-06	SA_REP	8600	.2	149	80
169	Harrison	Bloom	HBLOOM	011.44.1343.829268	23-MAR-06	SA_REP	10000	.2	148	80
143	Randall	Matos	RMATOS	650.121.2874	15-MAR-06	ST_CLERK	2600	-	124	50
112	Jose Manuel	Urman	JMURMAN	515.124.4469	07-MAR-06	FI_ACCOUNT	7800	-	108	100
181	Jean	Fleaur	JFLEAUR	650.507.9877	23-FEB-06	SH_CLERK	3100	-	120	50
139	John	Seo	JSEO	650.121.2019	12-FEB-06	ST_CLERK	2700	-	123	50
106	Valli	Pataballa	VPATABAL	590.423.4560	05-FEB-06	IT_PROG	4800	-	103	60
170	Tayler	Fox	TFOX	011.44.1343.729268	24-JAN-06	SA_REP	9600	.2	148	80
180	Winston	Taylor	WTAYLOR	650.507.9876	24-JAN-06	SH_CLERK	3200	-	120	50
103	Alexander	Hunold	AHUNOLD	590.423.4567	03-JAN-06	IT_PROG	9000	-	102	60
116	Shelli	Baida	SBAIDA	515.127.4563	24-DEC-05	PU_CLERK	2900	-	114	30
160	Louise	Doran	LDORAN	011.44.1345.629268	15-DEC-05	SA_REP	7500	.3	146	80
162	Clara	Vishney	CVISHNEY	011.44.1346.129268	11-NOV-05	SA_REP	10500	.25	147	80
130	Mozhe	Atkinson	MATKINSO	650.124.6234	30-OCT-05	ST_CLERK	2800	-	121	50
138	Stephen	Stiles	SSTILES	650.121.2034	26-OCT-05	ST_CLERK	3200	-	123	50
123	Shanta	Vollman	SVOLLMAN	650.123.4234	10-OCT-05	ST_MAN	6500	-	100	50
111	Ismael	Sciarra	ISCIARRA	515.124.4369	30-SEP-05	FI_ACCOUNT	7700	-	108	100
110	John	Chen	JCHEN	515.124.4269	28-SEP-05	FI_ACCOUNT	8200	-	108	100
101	Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	AD_VP	17000	-	100	90
129	Laura	Bissot	LBISSOT	650.124.5234	20-AUG-05	ST_CLERK	3300	-	121	50
152	Peter	Hall	PHALL	011.44.1344.478968	20-AUG-05	SA_REP	9000	.25	145	80
202	Pat	Fay	PFAY	603.123.6666	17-AUG-05	MK_REP	6000	-	201	20
189	Jennifer	Dilly	JDILLY	650.505.2876	13-AUG-05	SH_CLERK	3600	-	122	50
117	Sigal	Tobias	STOBIAS	515.127.4564	24-JUL-05	PU_CLERK	2800	-	114	30
125	Julia	Nayer	JNAYER	650.124.1214	16-JUL-05	ST_CLERK	3200	-	120	50
105	David	Austin	DAUSTIN	590.423.4569	25-JUN-05	IT_PROG	4800	-	103	60
188	Kelly	Chung	KCHUNG	650.505.1876	14-JUN-05	SH_CLERK	3800	-	122	50
121	Adam	Fripp	AFRIPP	650.123.2234	10-APR-05	ST_MAN	8200	-	100	50
151	David	Bernstein	DBERNSTE	011.44.1344.345268	24-MAR-05	SA_REP	9500	.25	145	80
175	Alyssa	Hutton	AHUTTON	011.44.1644.429266	19-MAR-05	SA_REP	8800	.25	149	80
168	Lisa	Ozer	LOZER	011.44.1343.929268	11-MAR-05	SA_REP	11500	.25	148	80
159	Lindsey	Smith	LSMITH	011.44.1345.729268	10-MAR-05	SA_REP	8000	.3	146	80
147	Alberto	Errazuriz	AERRAZUR	011.44.1344.429278	10-MAR-05	SA_MAN	12000	.3	100	80
193	Britney	Everett	BEVERETT	650.501.2876	03-MAR-05	SH_CLERK	3900	-	123	50
185	Alexis	Bull	ABULL	650.509.2876	20-FEB-05	SH_CLERK	4100	-	121	50
131	James	Marlow	JAMRLOW	650.124.7234	16-FEB-05	ST_CLERK	2500	-	121	50
150	Peter	Tucker	PTUCKER	011.44.1344.129268	30-JAN-05	SA_REP	10000	.3	145	80
142	Curtis	Davies	CDAVIES	650.121.2994	29-JAN-05	ST_CLERK	3100	-	124	50
146	Karen	Partners	KPARTNER	011.44.1344.467268	05-JAN-05	SA_MAN	13500	.3	100	80
145	John	Russell	JRUSSEL	011.44.1344.429268	01-OCT-04	SA_MAN	14000	.4	100	80
158	Allan	McEwen	AMCEWEN	011.44.1345.829268	01-AUG-04	SA_REP	9000	.35	146	80
120	Matthew	Weiss	MWEISS	650.123.1234	18-JUL-04	ST_MAN	8000	-	100	50
133	Jason	Mallin	JMALLIN	650.127.1934	14-JUN-04	ST_CLERK	3300	-	122	50
174	Ellen	Abel	EABEL	011.44.1644.429267	11-MAY-04	SA_REP	11000	.3	149	80
157	Patrick	Sully	PSULLY	011.44.1345.929268	04-MAR-04	SA_REP	9500	.35	146	80
201	Michael	Hartstein	MHARTSTE	515.123.5555	17-FEB-04	MK_MAN	13000	-	100	20
192	Sarah	Bell	SBELL	650.501.1876	04-FEB-04	SH_CLERK	4000	-	123	50
156	Janette	King	JKING	011.44.1345.429268	30-JAN-04	SA_REP	10000	.35	146	80
184	Nandita	Sarchand	NSARCHAN	650.509.1876	27-JAN-04	SH_CLERK	4200	-	121	50
141	Trenna	Rajs	TRAJS	650.121.8009	17-OCT-03	ST_CLERK	3500	-	124	50
200	Jennifer	Whalen	JWHALEN	515.123.4444	17-SEP-03	AD_ASST	4400	-	101	10
137	Renske	Ladwig	RLADWIG	650.121.1234	14-JUL-03	ST_CLERK	3600	-	123	50
100	Steven	King	SKING	515.123.4567	17-JUN-03	AD_PRES	24000	-	-	90
115	Alexander	Khoo	AKHOO	515.127.4562	18-MAY-03	PU_CLERK	3100	-	114	30
122	Payam	Kaufling	PKAUFLIN	650.123.3234	01-MAY-03	ST_MAN	7900	-	100	50
114	Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	PU_MAN	11000	-	100	30
108	Nancy	Greenberg	NGREENBE	515.124.4569	17-AUG-02	FI_MGR	12008	-	101	100
109	Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT	9000	-	108	100
206	William	Gietz	WGIETZ	515.123.8181	07-JUN-02	AC_ACCOUNT	8300	-	205	110
203	Susan	Mavris	SMAVRIS	515.123.7777	07-JUN-02	HR_REP	6500	-	101	40
204	Hermann	Baer	HBAER	515.123.8888	07-JUN-02	PR_REP	10000	-	101	70
205	Shelley	Higgins	SHIGGINS	515.123.8080	07-JUN-02	AC_MGR	12008	-	101	110
102	Lex	De Haan	LDEHAAN	515.123.4569	13-JAN-01	AD_VP	17000	-	100	90

Statement 2

select EMPLOYEE_ID  
  from hr.employees  
 where HIRE_DATE = (select max(HIRE_DATE)  from hr.employees) 
 order by 1 desc nulls last


EMPLOYEE_ID
173
167

2 rows selected.

EMPLOYEE_ID
173
167

Statement 3

select max(EMPLOYEE_ID ) 
  from ( 
        select dense_rank() over(order by HIRE_DATE desc nulls last) d_rnk,EMPLOYEE_ID 
         from  hr.employees )  
where d_rnk = 1


MAX(EMPLOYEE_ID)
173

MAX(EMPLOYEE_ID)
173

Statement 4

select max(EMPLOYEE_ID) keep(dense_rank FIRST order by HIRE_DATE desc nulls last ) 
from hr.employees


MAX(EMPLOYEE_ID)KEEP(DENSE_RANKFIRSTORDERBYHIRE_DATEDESCNULLSLAST)
173

MAX(EMPLOYEE_ID)KEEP(DENSE_RANKFIRSTORDERBYHIRE_DATEDESCNULLSLAST)
173

Statement 5

select max(EMPLOYEE_ID) keep(dense_rank FIRST order by HIRE_DATE) 
from hr.employees


MAX(EMPLOYEE_ID)KEEP(DENSE_RANKFIRSTORDERBYHIRE_DATE)
102

MAX(EMPLOYEE_ID)KEEP(DENSE_RANKFIRSTORDERBYHIRE_DATE)
102

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database