You can translate your requirement into : SELECT route_id [ROUTE_ID] FROM route_master(NOLOCK) WHERE route_ou = 2 AND (@l_s_query is null OR route_query = @l_s_query) AND lang_id = 1 OPTION (RECOMPILE) The OPTION (RECOMPILE) is optional but can give better execution plans at the expense of extra compilation time as discussed in the canonical article on … Read more
The only viable solution in my opinion is to use an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value a computed, persisted column to convert that numeric value to the value you need So try this: CREATE TABLE dbo.tblUsers (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY … Read more
simply put, EXISTS is usually used for checking whether rows that meet a criteria exist in another (or the same) table. your SQL using EXISTS would look like this: select * from emp e where exists(select * from emp e2 where e.empno = e2.empno and e2.ename in (‘smith’, ‘brown’, ‘john’, ‘johnson’)) so you can see … Read more
You can use analytic functions SELECT * FROM (SELECT c.*, rank() over (partition by user_id order by ts desc) rnk FROM comments c) WHERE rnk = 1 Depending on how you want to handle ties (if there can be two rows with the same user_id and ts), you may want to use the row_number or … Read more
Ok if you mean show something like Prev 1 2 3 4 5 6 .. 40 41 Next Prev 1 2 .. 6 7 8 9 10 .. 40 41 Next First thing we need to is create a function that can process what we need to output the pagination. Heres a function I use … Read more
While it seems somewhat awkward, this is what can be done to achieve the goal: SELECT FLOOR(10000 + RAND() * 89999) AS random_number FROM table WHERE random_number NOT IN (SELECT unique_id FROM table) LIMIT 1 Simply put, it generates N random numbers, where N is the count of table rows, filters out those already present … Read more
Expanding on a_horse_with_no_name’s answer, this show how to use SQL Server’s implementation of recursive CTE (recursive single-record cross apply) in combination with row_number() to produce the exact output in the question. declare @t table(id int,parentId int,name varchar(20)) insert @t select 1, 0 ,’Category1′ insert @t select 2, 0, ‘Category2’ insert @t select 3, 1, ‘Category3’ … Read more
No, it is not possible. PREPARE / EXECUTE stmt can execute only one query at a time, many statements cannot be combined. See documentation: http://dev.mysql.com/doc/refman/5.0/en/prepare.html … a user variable that contains the text of the SQL statement. The text must represent a single statement, not multiple statements. Anyway, to simplify your code I would create … Read more
To get all birthdays in next 7 days, add the year difference between the date of birth and today to the date of birth and then find if it falls within next seven days. SELECT * FROM persons WHERE DATE_ADD(birthday, INTERVAL YEAR(CURDATE())-YEAR(birthday) + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0) YEAR) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY); If … Read more
With a little more information I can get a more helpful answer. If you are using MySQL: stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); from http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html: java.util.Properties info = new java.util.Properties(); info.put (“user”, “scott”); info.put (“password”,”tiger”); info.put (“defaultRowPrefetch”,”15″); getConnection (“jdbc:oracle:oci:@”,info);