How do I limit the number of rows returned by an Oracle query after ordering? Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically. In Oracle, the only thing people

Best way to get identity of inserted row? What is the best way to get identity of inserted row? I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don't understand the pros and cons attached to each. Can someone please explain the differences and when I should be using each? Answer: @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

How can I do an UPDATE statement with JOIN in SQL? I need to update this table in SQL Server 2005 with data from its 'parent' table, see below: sale id (int) udid (int) assid (int) ud id (int) assid (int) sale.assid contains the correct value to update ud.assid. What query will do this? I'm thinking a join but I'm not sure if it's possible. Answer : It very much depends on which SQL DBMS you're using. Here are some ways to do it in

Get list of all tables in Oracle? How do I query an Oracle database to display the names of all tables in it? answer: SELECT owner, table_name FROM dba_tables assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table or that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary

Finding duplicate values in a SQL table It's easy to find duplicates with one field: SELECT name, COUNT(email) FROM users GROUP BY email HAVING ( COUNT(email) > 1 ) So if we have a table ID NAME EMAIL 1 John asd@asd.com 2 Sam asd@asd.com 3 Tom asd@asd.com 4 Bob bob@asd.com 5 Tom asd@asd.com This query will give us John, Sam, Tom, Tom because they all have the same email. However, what I want is to get duplicates with the same email and name. That is, I want