Home » SQL & PL/SQL » SQL & PL/SQL » How to get minimum value in a single SQL from 3 tables having same structure and different values
How to get minimum value in a single SQL from 3 tables having same structure and different values [message #224672] Thu, 15 March 2007 01:54 Go to next message
abhijit.roy
Messages: 3
Registered: March 2007
Location: Kolkata
Junior Member
I want to retrieve the name who have joined earliest from the three locations.

table: emp_kol

NAME DOJ
---------- ---------
anuj 01-JAN-07
kajol 01-MAY-02


table: emp_mum

NAME DOJ
---------- ---------
samrat 01-JAN-00
Banti 01-MAR-01


table: emp_pune

NAME DOJ
---------- ---------
rajib 01-JUN-01
kinnan 01-DEC-05

----------------------
The required output will be as follows-

NAME DOJ
---------- -----------
samrat 01-JAN-00
Re: How to get minimum value in a single SQL from 3 tables having same structure and different value [message #224675 is a reply to message #224672] Thu, 15 March 2007 02:10 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can use union on all the tables, but select DOJ as the first column in the select.
In the outer select you can get the first row.
select * 
  from ( select doj,name from emp_pune
          union
         select doj,name from emp_mum
          union
         select doj,name from emp_kol
        )
 where rownum = 1;
But this will give an(y) employee, who joined on the earliest date you have in those tables.

If you want to have all the employees, who joined on the earliest date, I think you have to use min(doj) (or) "rownum = 1" in the outer select.
On top of that, we have to have a filter on doj after union again.
From 9i you can use "with"

By
Vamsi
Re: How to get minimum value in a single SQL from 3 tables having same structure and different value [message #225678 is a reply to message #224672] Tue, 20 March 2007 22:19 Go to previous messageGo to next message
Tanveer_ht
Messages: 7
Registered: March 2007
Junior Member
Hi,

This Query will return the Desired Output to U Abhijit.
If u want Recent Employee just change the Order by to Descending.



SELECT *
FROM (
SELECT * FROM emp_del
UNION
(
SELECT * FROM emp_mum
UNION
SELECT * FROM emp_chennai
)
ORDER BY 2
)
WHERE ROWNUM=1
/

From
Tanveer Islam Siddiqui

[Updated on: Tue, 20 March 2007 22:31]

Report message to a moderator

Re: How to get minimum value in a single SQL from 3 tables having same structure and different value [message #228565 is a reply to message #225678] Tue, 03 April 2007 06:46 Go to previous messageGo to next message
gokanidisha
Messages: 2
Registered: March 2007
Location: pune
Junior Member

Mad

hi u can do this by following query

SELECT * FROM ( SELECT * FROM DATE_1 UNION SELECT * FROM DATE_2 UNION SELECT * FROM DATE_3 )WHERE ROWNUM=1
ORDER BY DOJ DESC

consider date_1 ,date_2 and date_3 as name of your table
Re: How to get minimum value in a single SQL from 3 tables having same structure and different value [message #228573 is a reply to message #228565] Tue, 03 April 2007 07:17 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
This won't work.
Either you need to use "order by" for inner query or select the doj as the first column, so that union will take care of the order by.

By
Vamsi
Re: How to get minimum value in a single SQL from 3 tables having same structure and different value [message #228601 is a reply to message #228573] Tue, 03 April 2007 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Wrong, UNION does not guarantee any order, it just guarantees unique values.
If you want an order you ALWAYS have to give an ORDER BY clause.

Regards
Michel
Re: How to get minimum value in a single SQL from 3 tables having same structure and different value [message #228623 is a reply to message #228601] Tue, 03 April 2007 09:02 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
agreed...but not Embarassed
union vs order by
but i have seen a controdictory statement.
another one

I, myself got confused. Mad

By
Vamsi
Re: How to get minimum value in a single SQL from 3 tables having same structure and different value [message #228627 is a reply to message #228623] Tue, 03 April 2007 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No confusion.
Maybe in some versions UNION uses a sort to eliminate duplicates but it has always been a BINARY sort which seems to give a correct order in some cases (positive numbers for instance) but not in other ones (strings with a non american language/character set for instance).
Now, with 10g, UNION does not need a sort (in some cases not all ones) and this is compliant with its definition and relational model.

Regards
Michel

Re: How to get minimum value in a single SQL from 3 tables having same structure and different value [message #228636 is a reply to message #228627] Tue, 03 April 2007 09:33 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
want to add a bit. Cool
By
Vamsi
Previous Topic: VB6 and oracle connection
Next Topic: How to read and create XML using XMLDB built in
Goto Forum:
  


Current Time: Mon Dec 05 19:15:13 CST 2016

Total time taken to generate the page: 0.13276 seconds