You can use a subquery for this like select * from ( select * from emp order by sal desc ) where ROWNUM <= 5; Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information. Update: To limit the result with both lower and upper bounds things get … Read more
They are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.
@@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. SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and … Read more
There are a few steps to see the tables in an SQLite database: List the tables in your database: .tables List how the table looks: .schema tablename Print the entire table: SELECT * FROM tablename; List all of the available SQLite prompt commands: .help
Group By X means put all those with the same value for X in the one group. Group By X, Y means put all those with the same values for both X and Y in the one group. To illustrate using an example, let’s say we have the following table, to do with who is … Read more
You can use GROUP_CONCAT: SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ‘, ‘) FROM peoples_hobbies GROUP BY person_id; As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates: SELECT person_id, GROUP_CONCAT(DISTINCT hobbies SEPARATOR ‘, ‘) FROM peoples_hobbies GROUP BY person_id; As Jan stated in their comment, you can also sort the values before … Read more
Syntax strictly depends on which SQL DBMS you’re using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you’re using a SQL DBMS other … Read more
You can reset the counter with: ALTER TABLE tablename AUTO_INCREMENT = 1 For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel): Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if … Read more
My favorite answer is as what the first sentence in this thread suggested. Use an Adjacency List to maintain the hierarchy and use Nested Sets to query the hierarchy. The problem up until now has been that the coversion method from an Adjacecy List to Nested Sets has been frightfully slow because most people use … Read more
At first glance… All you need is a GROUP BY clause with the MAX aggregate function: SELECT id, MAX(rev) FROM YourTable GROUP BY id It’s never that simple, is it? I just noticed you need the content column as well. This is a very common question in SQL: find the whole data for the row … Read more