Home » SQL & PL/SQL » SQL & PL/SQL » sorting by same date type column in different tables (oracle database 9i)
sorting by same date type column in different tables [message #413485] Thu, 16 July 2009 01:03 Go to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
Hi

I have two different tables both with 10 columsn (8 varchar , 1 date , 1 number)
but the names are different in both for columns.
Now I have to get the rows of both the tables altogether sorted by the time in the date columns

like
table 1 has a row with timestamp as 1100 (say)
and table 2 has timestamp as 1200

then the data in the output should contain data from row of tabel 1 and then row of data form table 2

Sapan
Re: sorting by same date type column in different tables [message #413487 is a reply to message #413485] Thu, 16 July 2009 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"order by <column number in select>"

Regards
Michel
Re: sorting by same date type column in different tables [message #413489 is a reply to message #413485] Thu, 16 July 2009 01:21 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
10 columsn (8 varchar , 1 date , 1 number)


Quote:
sorted by the timeb in the date columns


Do you want sorting only on time portion of date? or whole date column?

regards,
Delna
Re: sorting by same date type column in different tables [message #413496 is a reply to message #413487] Thu, 16 July 2009 01:45 Go to previous messageGo to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
but there would be two columns from two different tables
could you please explain a little more

eg
table1(name, place, time)
table2(newname, newplace, timestamp)

sorting by time and timestamp
Re: sorting by same date type column in different tables [message #413498 is a reply to message #413489] Thu, 16 July 2009 01:46 Go to previous messageGo to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
Hi delna

I want it on complete date columns
Re: sorting by same date type column in different tables [message #413500 is a reply to message #413485] Thu, 16 July 2009 01:59 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
As suggested by Michel sir
and what you have provided

SELECT * FROM
 (SELECT NAME, PLACE, TIME FROM TABLE1
  UNION
  SELECT NEWNAME, NEWPLACE, TIMESTAMP FROM TABLE2)
ORDER BY 3


regards,
Delna
Re: sorting by same date type column in different tables [message #413523 is a reply to message #413500] Thu, 16 July 2009 04:38 Go to previous messageGo to next message
member.forums@gmail.com
Messages: 28
Registered: June 2009
Location: India
Junior Member
Yeah thanks

that works just fine
but is the outer select required
it works fine without it for me
Re: sorting by same date type column in different tables [message #413541 is a reply to message #413485] Thu, 16 July 2009 05:35 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
UNION sorts data by default.
But I think sort applies on first column.

Can you provide your query and its output?

regards,
Delna
Re: sorting by same date type column in different tables [message #413545 is a reply to message #413541] Thu, 16 July 2009 05:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You cannot rely on anything to perform a sort except ORDER BY.

From the documentation:
Quote:
order_by_clause

Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
Re: sorting by same date type column in different tables [message #413548 is a reply to message #413485] Thu, 16 July 2009 05:53 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes JRowbottom sir,
That is what I was trying to say.
And that is why, I asked for query and output of that query.

regards,
Delna
Re: sorting by same date type column in different tables [message #413550 is a reply to message #413548] Thu, 16 July 2009 06:41 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
delna.sexy wrote on Thu, 16 July 2009 11:53
Yes JRowbottom sir,
That is what I was trying to say.


But it's really NOT what you actually said

Quote:
UNION sorts data by default.
But I think sort applies on first column.



Re: sorting by same date type column in different tables [message #413564 is a reply to message #413523] Thu, 16 July 2009 07:50 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
member.forums@gmail.com wrote on Thu, 16 July 2009 05:38
Yeah thanks

that works just fine
but is the outer select required
it works fine without it for me


correct, you do not need the outer SELECT. A simple UNION would suffice, but you should use a UNION ALL instead.
Previous Topic: SQL query using NVL
Next Topic: Very new to SQL... syntax error problem
Goto Forum:
  


Current Time: Sat Dec 03 11:56:40 CST 2016

Total time taken to generate the page: 0.08144 seconds