New answers tagged select
Use SQL_CALC_FOUND_ROWS in your SELECT query and then run SELECT FOUND_ROWS(): SELECT SQL_CALC_FOUND_ROWS field_name,filed_name2 FROM col WHERE CLAUSE LIMIT X; SELECT FOUND_ROWS() ;
select isnull(column,1) from table
You don't need to do anything to get statement level consistency A query always sees a consistent state of the database regardless of the isolation level you use. Quote from the manual: This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. ...
Read committed (especially section 13.2.1) is the default read level in PostgreSQL. This read level will give you a snapshot of what has been committed before your transaction starts. It will allow other transactions to read and write to your table, you just won't be able to see any writes made after the start of your transaction. Does this only apply ...
I'm guessing this is what you're after. SELECT MIN(ID) AS ID, Name, MyID FROM tablename GROUP BY MyID, Name; If Name can differ between two MyID, it gets a bit more complicated. SELECT ID, Name, MyID FROM ( SELECT ID, Name, MyID, ROW_NUMBER() OVER (PARTITION BY MyID ORDER BY ID) AS _rn FROM tablename ) AS sub WHERE _rn=1;
This query with a DISTINCT: SELECT DISTINCT Name, MyID FROM data; Returns distinct Name and MyID: Name | MyID Alan | 2 John | 3 This query with a GROUP BY: SELECT MIN(ID) AS ID, Name, MyID FROM data GROUP BY Name, MyID; Returns Name and MyId with the smallest ID like your sample: ID | Name | MyID 1 | Alan | 2 3 | ...
Vertica query performance depends highly on the predicate used in the query . To get the gist of your performance , try getting the projection name of the selected columns of the query you are firing . The columns in the order by clause of the projection is very important in deciding the performance of your select. you can get that by running explain on ...
Simply add another column, order by abs(connection-5), abs(level-4) is valid syntax: select lobby.id, lobby.user_id, users.connection, users.level from lobby INNER JOIN users ON lobby.user_id = users.id where lobby.user_id <> 1 and users.connection between 0 and 10 and users.level between 1 and 11 order by abs(connection-5), abs(level-4);
Try something like this: SELECT l.id ,l.user_id # current user ID ,others.id # other user's ID ,others.connection ,others.level FROM lobby l INNER JOIN users AS myuser ON l.user_id = myuser.id INNER JOIN users AS others # Remove l.user_id = others.id ON myuser.id <> others.id AND ABS(myuser.connection - ...
This is faster SELECT SUM (row_count) FROM sys.dm_db_partition_stats WHERE object_id=OBJECT_ID('Users') AND (index_id<2);
Top 50 recent answers are included