I have written sql query     Select * from Products WHERE Products.Category=="Fruits";   the returned answer is    1-Banana   2-Mango   3-Apple   4-Oranges   5-Grapes   now i want to select fruit at index 3rd only that is Apple using sql indexes  the problem arises every where how should i select the element at 3rd place using its index in sql query?
49941
I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases:    SQL Server MySQL PostgreSQL SQLite Oracle   I am currently doing something like the following in SQL Server 2005, but I'd be interested in seeing other's more agnostic approaches:  WITH Ordered AS ( SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate FROM Orders) SELECT * FROM Ordered WHERE RowNumber = 1000000   Credit for the above SQL:   Update: See  regarding the SQL standard. Troels, have you got any links we can cite?
59876
Out of the following queries, which method would you consider the better one? What are your reasons (code efficiency, better maintainability, less WTFery)...  SELECT MIN(`field`) FROM `tbl`;  SELECT `field` FROM `tbl` ORDER BY `field` LIMIT 1;
86274
Here's a scenario:  Someone asked about "How to build a SQL query to get count and blah blah blah..."  His only tag is . I added the  and  tags to it.  Someone else answered with a nice SQL statement that has a "group by" statement which seems "logically inevitable".  I proceed to add a tag . I did that on the basis that someone who searches for  will be able to find this post useful, just as I did. My concern is, it wasn't described at all in the question, nor was it implied, unlike  and .  What is the community opinion?  The scenario above is a real one . And the one who answered it nicely is Jon Skeet.
45618
I was browsing through the questions and noticed this:  SELECT prodid, issue FROM Sales  WHERE custid = @custid  AND datesold = SELECT MAX(datesold)               FROM Sales s               WHERE s.prodid = Sales.prodid                   AND s.issue = Sales.issue                   AND s.custid = @custid   I was wondering what the "@" does in front of custID?  Is it just a way of referencing the custID from the table being selected?
51549
What's wrong with this query: INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;  It works without the WHERE clause. I've seemed to have forgot my SQL.
45432
I've asked the same question on , but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice).  Suppose I have 3 tables: posts, post_categories and categories. I'm implementing some kind of page with filters where I can filter posts by categories. User can select multiple categories. When he selects more than one, they should be summed. I can't get it working. What I have now is simple IN() SQL clause:  SELECT posts.id, post_categories.category_id FROM posts  JOIN post_categories ON posts.id = post_categories.post_id  WHERE post_categories.category_id IN (1,2,3) LIMIT 10;   But it is not matching all ids, it is OR and I need AND. What I need here is to find all posts that have categories with id=1 AND id=2 AND id=3, but instead this query returns posts that have at least one category from the IN() list.
98204
During my work with databases I noticed that I write query strings and in this strings I have to put several restrictions in the where-clause from a list/array/collection. Should look like this:  select * from customer  where customer.id in (34, 26, ..., 2);   You can simplify this by reducing this to the question that you have collection of strings and want to create a comma-separated list of this strings in just one string.  My approach I have used so far is something like that:  String result = ""; boolean first = true; for(String string : collectionOfStrings) {     if(first) {         result+=string;         first=false;     } else {         result+=","+string;     } }   But this is as you can see very ugly. You cannot see what happens there on the first look, especially when the constructed strings (like every SQL query) is getting complicated.  What is your (more) elegant way?
83102
Let's examine these two statements:  IF (CONDITION 1) OR (CONDITION 2) ...  IF (CONDITION 3) AND (CONDITION 4) ...   If CONDITION 1 is TRUE, will CONDITION 2 be checked? If CONDITION 3 is FALSE, will CONDITION 4 be checked?  What about conditions on WHERE: does the SQL Server engine optimize all conditions in a WHERE clause? Should programmers place conditions in the right order to be sure that the SQL Server optimizer resolves it in the right manner?     ADDED:  Thank to Jack for link, surprise from t-sql code:  IF  1/0 = 1 OR 1 = 1       SELECT 'True' AS result ELSE       SELECT 'False' AS result   IF  1/0 = 1 AND 1 = 0       SELECT 'True' AS result ELSE       SELECT 'False' AS result   There is not raise a Divide by zero exception in this case.  CONCLUSION:  If C++/C#/VB has short-circuiting why can't SQL Server have it?     To truly answer this let's take a look at how both work with   conditions. C++/C#/VB all have short circuiting defined in the   language specifications to speed up code execution. Why bother   evaluating  N OR conditions when the first one is already true or M   AND conditions when the first one is already false.      We as developers have to be aware that SQL Server works differently.   It is a cost based system. To get the optimal execution plan for our   query the query processor has to evaluate every where condition and   assign it a cost. These costs are then evaluated as a whole to form a   threshold that must be lower than the defined threshold SQL Server has   for a good plan. If the cost is lower than the defined threshold the   plan is used, if not the whole process is repeated again with a   different mix of condition costs. Cost here is either a scan or a seek   or a merge join or a hash join etc... Because of this the   short-circuiting as is available in C++/C#/VB simply isn't possible.   You might think that forcing use of index on a column counts as short   circuiting but it doesn't. It only forces the use of that index and   with that shortens the list of possible execution plans. The system is   still cost based.      As a developer you must be aware that SQL Server does not do   short-circuiting like it is done in other programming languages and   there's nothing you can do to force it to.
24654
Is there any performance difference between these two queries?  select name from Account where id = :myId  select name from Account where id = :myId limit 1   Or, I guess more general: does salesforce stop searching after first hit when there's a unique value as a condition?
94031
I have a query in a before-update trigger that looks like following:  [SELECT Id,(SELECT Id,Active_opp__c              FROM Opportunities              ORDER BY Days_from_Last_Stage__c ASC)   FROM Account WHERE Id IN: conAccMap.values()]   I have checked this in query plan and it looks like :    Is there a chance that the inner query will cause selective query error since I dont have a WHERE Clause on an indexed field in the inner query?
57472
I would like to construct a query that displays all the results in a table, but is offset by 5 from the start of the table. As far as I can tell, MySQL's LIMIT requires a limit as well as an offset. Is there any way to do this?
64730
--Setup, actual queries after the space --create table fltrind (a integer,b integer) truncate table fltrind DROP INDEX fltrind.nf DROP INDEX fltrind.f DECLARE @i integer SET @i=0 while @i&lt;100 BEGIN INSERT INTO fltrind(a) values(@i) SET @i=@i+1 END INSERT INTO fltrind(a,b) values(9000,0900)  insert into fltrind(a,b) select top 100000 f1.a,f1.b from fltrind f1 , fltrind f2  create nonclustered index nf on fltrind(b) INCLUDE(a) create nonclustered index f on fltrind(b) INCLUDE(a) where b is not null  UPDATE STATISTICS fltrind WITH FULLSCAN  select a from fltrind where b is not null  DROP INDEX fltrind.nf  UPDATE STATISTICS fltrind WITH FULLSCAN  select a from fltrind where b is not null   Looking at the query plan, the filtered index is not used as long as the non-filtered index is present.  Any idea why, and how can I get it to use the filtered index?  Dropping the non filtered index nf makes the optimizer use the filtered index f.  In fact, increasing the rowcount so that >10% of the rows qualify results in a table scan when the non filtered index is dropped.
58239
I have the following query:  SELECT c.* FROM companies AS c JOIN users AS u USING(companyid) JOIN jobs AS j USING(userid) JOIN useraccounts AS us USING(userid) WHERE j.jobid = 123;   I have the following questions:   Is the USING syntax synonymous with ON syntax? Are these joins evaluated left to right?  In other words, does this query say: x = companies JOIN users; y = x JOIN jobs; z = y JOIN useraccounts; If the answer to question 2 is yes, is it safe to assume that the companies table has companyid, userid and jobid columns? I don't understand how the WHERE clause can be used to pick rows on the companies table when it is referring to the alias "j"   Any help would be appreciated!
103055
Please look at this code:  create table #t1(   id int identity (1,1),   val varchar(10) );   insert into #t1 values ('a'); insert into #t1 values ('b'); insert into #t1 values ('c'); insert into #t1 values ('d');   Now, whenever you execute this  select *,      ( select top 1 val from #t1 order by NEWID()) rnd  from #t1 order by 1;   you will get a result with where all rows have the same random value. e.g.  id          val        rnd ----------- ---------- ---------- 1           a          b 2           b          b 3           c          b 4           d          b   I know a way using a cursor to loop throw the rows and get different random values, but that is not performant.   A clever solution to this is   select t1.id, t1.val, t2.val from #t1 t1     join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on  t1.id = t2.lfd    But I simplified the query. The real query looks more like  select *,      ( select top 1 val from t2 where t2.x &lt;&gt; t1.y order by NEWID()) rnd  from t1 order by 1;   and the simple solution doesn't fit. I'm looking for a way to force repeated evaluation of  ( select top 1 val from #t1 order by NEWID()) rnd    without the use of cursors.  Edit: Wanted output:  perhaps 1 call  id          val        rnd ----------- ---------- ---------- 1           a          c 2           b          c 3           c          b 4           d          a   and a second call  id          val        rnd ----------- ---------- ---------- 1           a          a 2           b          d 3           c          d 4           d          b   The value for each row just should be a random value independent from the other rows  Here is the cursor version of the code:  CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));  DECLARE @id INT DECLARE @val VARCHAR(10) DECLARE c CURSOR FOR SELECT id, val FROM #t1 OPEN c FETCH NEXT FROM c INTO @id, @val WHILE @@FETCH_STATUS = 0 BEGIN     INSERT INTO #res     SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd      FETCH NEXT FROM c INTO @id, @val END CLOSE c DEALLOCATE c  SELECT * FROM #res
27703
Is it possible to use an IF clause within a WHERE clause in MS SQL?  Example:  WHERE     IF IsNumeric(@OrderNumber) = 1         OrderNumber = @OrderNumber     ELSE         OrderNumber LIKE '%' + @OrderNumber + '%'
21234
I am wondering if there is away (possibly a better way) to order by the order of the values in an IN() clause.  The problem is that I have 2 queries, one that gets all of the IDs and the second that retrieves all the information. The first creates the order of the IDs which I want the second to order by. The IDs are put in an IN() clause in the correct order.  So it'd be something like (extremely simplified):  SELECT id FROM table1 WHERE ... ORDER BY display_order, name  SELECT name, description, ... WHERE id IN ([id's from first])   The issue is that the second query does not return the results in the same order that the IDs are put into the IN() clause.  One solution I have found is to put all of the IDs into a temp table with an auto incrementing field which is then joined into the second query.  Is there a better option?  Note: As the first query is run "by the user" and the second is run in a background process, there is no way to combine the 2 into 1 query using sub queries.  I am using MySQL, but I'm thinking it might be useful to have it noted what options there are for other DBs as well.
83319
is there a quick way to sort the items of a select element? Or I have to resort to writing javascript?  Please any ideas.  &lt;select size="4" name="lstALL" multiple="multiple" id="lstALL" tabindex="12" style="font-size:XX-Small;height:95%;width:100%;"&gt; &lt;option value="0"&gt; XXX&lt;/option&gt; &lt;option value="1203"&gt;ABC&lt;/option&gt; &lt;option value="1013"&gt;MMM&lt;/option&gt; &lt;/select&gt;