Tag Archives: SQL

Select random rows from a table in SQL

A friend of mine recently asked me how to select random rows from a table using a SQL query which let me to find a solution. If you are wondering why we need to select rows randomly from a table, consider a simple example of an online quiz which needs to display the questions in a random order for each person who is going to take the exam.

After a bit of research and googling I found the answer and then thought of sharing it here so that I can refer it at a later point of time and at the same time it may be useful for others who are searching for a similar solution.

In Oracle we have to use the sys_guid() function. The query will look like

SELECT col1, col2 FROM tblname ORDER BY SYS_GUID()

In SQL Server the query would look like

SELECT col1, col2 FROM tblname ORDER BY NEWID()

In MySQL the query would look like (Thanks Aswin)

SELECT col1, col2 FROM tblname ORDER BY RAND()

If any of you know how to do this in other databases or have a better solution than this, leave it the comments so that I can mention them here.

Posted in Database Programming | Tagged , , | 2 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