how to randomly select a row from a table having more then one row with same id
Hi all,
I have a table Employee having columns as EmpId , EmpName and ProjectName. An Employee can be involved in more than one project, So the table structure is as below:
EmpId EmpName ProjectName
1 jane P1
1 jane p2
1 jane p3
2 david p1
2 david p4
Now I want that randomly one row should be retrieve from the table against a particular EmpId.
How could I do that?
Thanks in Advance!!!!!!!!
# 1 Re: how to randomly select a row from a table having more then one row with same id
You will have to use a WHERE clause.
SELECT * FROM table WHERE EmpId = '1' AND ProjectName = 'p2'
# 2 Re: how to randomly select a row from a table having more then one row with same id
If you are using MSSQL this would do the trick:
SELECT TOP 1 * FROM table WHERE EmpId = '1' ORDER BY NEWID()
# 4 Re: how to randomly select a row from a table having more then one row with same id
Finally, If you're using Oracle you should write:
SELECT *
FROM table
WHERE id = 1
AND ROWNUM = 1
ROWNUM is a pseudo-column of recordsets, and it enumerates the selected rows.
# 6 Re: how to randomly select a row from a table having more then one row with same id
Finally, If you're using Oracle you should write:
ROWNUM is a pseudo-column of recordsets, and it enumerates the selected rows.
I think you'll find you have to randomize the rows order too:
SELECT * FROM
(
SELECT *
FROM table
WHERE id = 1
ORDER BY dbms_random.value
)
WHERE ROWNUM = 1
cjard at 2007-11-9 13:50:38 >
# 7 Re: how to randomly select a row from a table having more then one row with same id
You can check this blog post (from my blog) about returning random records from MS SQL Server and MySQL:
Returning random records.. MS SQL Server vs. MySQL ( http://forloveofsoftware.blogspot.com/2007/03/returning-randon-records-ms-sql-server.html)
hspc at 2007-11-9 13:51:43 >
