Tag Archives: Oracle

Oracle query to find the server start up time

One of my colleagues showed me this gem in Oracle and I thought of documenting it here so that it will be useful for everyone and also I can find it next time I need it by just searching my blog instead of Google. 🙂 Using this query you can find out the start time of the Oracle database server.

SELECT * FROM V$INSTANCE

In addition to the start time the query also returns other useful information like instance name, version, hostname etc. The hostname is particularly useful when TNSNAMES.ORA is playing tricks with you.

Posted in Database Programming | Tagged , | 4 Comments

Self-Join sub query

I came across a unique requirement at work for which I struggled a lot to write the SQL query. Then after some googling and tips from my DBA friends I finally came up with the query. I never came across this query before, so I thought of posting about it here, since some of you might benefit from it.

Since I cannot tell you the exact requirement or the table structure I mapped it with the classic emp table to illustrate the query.

Ok so consider the following table structure

SEQ_ID - unique sequence number
EMP_ID - unique emp id
DEP_ID - department id (from a master table)
SAL    - salary

And now the requirement is to find the employee with the max salary in each department.

The query

SELECT dep_id, MAX(sal) FROM tbl_name GROUP BY dep_id

will give me the maximum salary in each department, but now the real problem is to find out the person with that maximum salary in each department.

So finally I came up with this query

SELECT * FROM tbl_name WHERE (dept_id, sal) IN (SELECT dep_id, MAX(sal) FROM tbl_name GROUP BY dept_id)

The reason why I found this syntax to be strange for me is that I have never used two parameters in the IN clause.

The above query works, (atleast for me in Oracle 9i) but I am not sure whether it is the most optimal way of doing it. So if any of you know a better solution then do let me know.

Update:Jeff in the comments has specified that the equivalent in SQL Server is

SELECT A,* FROM tbl_name A
INNER JOIN (SELECT dept_id, MAX(Sal) AS Salary FROM tbl_name GROUP BY dept_id) B
ON B.dept_id = A.dept_id AND B.Sal = A.Sal

Posted in Database Programming | Tagged , , , | 2 Comments

Two companies change hands

I was reading Robert Scoble and he reports that two companies are changing hands. First Skype is sold to eBay and Oracle buys Siebel.

He also discuss about the advantages that eBay may get by using Skype.

Some people have come up to me and asked me “does this deal make any sense to you?” It does. eBay is a marketplace. It’s about putting buyers and sellers together. Now, how can you make that marketplace more efficient? Voice and video.

Selling things is easier when you can tell and show. Skype is all about that.

Can’t wait to see how this is going to change the way people buy things online.
Hopefully at least this is true, not like the Technorati rumor

Posted in Random/Personal | Tagged , , , , | Leave a comment