I can get second highest salary by this query. is it possible to use this query for calculate fifth highest salary??       SELECT max(salary)  FROM [Employee]  where (EmployeeID not in    (SELECT max(EmployeeID ) FROM [Employee] ))   please help me...
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?
80779
How to 'group by' a query using an alias, for example:  select count(*), (select * from....) as alias_column  from table  group by alias_column   I get 'alias_column' : INVALID_IDENTIFIER error message. Why? How to group this query?
26123
I'm trying to perform a group by action on an aliased column (example below) but can't determine the proper syntax.  SELECT       LastName + ', ' + FirstName AS 'FullName' FROM         customers GROUP BY     'FullName'   What is the correct syntax?   EDIT  Extending the question further (I had not expected the answers I had received) would the solution still apply for a CASEed aliased column?  SELECT            CASE         WHEN LastName IS NULL THEN FirstName         WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName     END AS 'FullName' FROM         customers GROUP BY          LastName, FirstName   And the answer is yes it does still apply.
114266
I tried to find this question somewhere else on here, and found a couple that were similar to it -- but all the answers seemed pretty convoluted, and I think it was surprising to me because I just imagined that SQL would have a way easier way to do this.  Essentially, I'm using a GROUP BY to group two values on top of each other. The coding is simple and works well, where I get these results using WITH ROLLUP at the end:  Type    Person  Count Type A  Person 1    10 Type A  Person 2    91 Type A  Person 3    13 Type A  Person 4    10 Type A  Person 5    2 Type A  NULL        126 Type B  Person 6    16 Type B  Person 7    63 Type B  Person 8    15 Type B  Person 9    22 Type B  Person 10   16 Type B  Person 11   3 Type B  Person 12   20 Type B  Person 13   44 Type B  NULL        198 NULL    NULL        360   Is there an easy way to substitute something saying if "NULL" is in the "Person" column, then make NULL AS "Subtotal" and if "NULL" is in the "Type" column, NULL AS "Grand Total?"  And if that's weirdly complicated, I'd be totally fine with just calling all of them "Total."  Thanks!
46559
One of my columns is called from. I can't change the name because I didn't make it. Am I allowed to do something like SELECT from FROM TableName or is there a special syntax to avoid the SQL Server being confused?
78559
Not sure if this is a duplicate question, How do I find out my all time best ranking? Since ranking move up and down, Is there a way to find out my all time best rank? In case we do not have it, I suggest we take this as a request proposal and provide this feature for the users.
79420
If I have a table  CREATE TABLE users (   id int(10) unsigned NOT NULL auto_increment,   name varchar(255) NOT NULL,   profession varchar(255) NOT NULL,   employer varchar(255) NOT NULL,   PRIMARY KEY  (id) )   and I want to get all unique values of profession field, what would be faster (or recommended):  SELECT DISTINCT u.profession FROM users u   or  SELECT u.profession FROM users u GROUP BY u.profession   ?
16353
I have this MySQL query:  SELECT DAYOFYEAR(`date`)  AS d, COUNT(*)  FROM  `orders`  WHERE  `hasPaid` &gt; 0 GROUP  BY d ORDER  BY d   Which returns something like this:  d  | COUNT(*) | 20 |  5       | 21 |  7       | 22 | 12       | 23 |  4       |   What I'd really like is another column on the end to show the running total:  d  | COUNT(*) | ??? | 20 |  5       |   5 | 21 |  7       |  12 | 22 | 12       |  24 | 23 |  4       |  28 |   Is this possible?
67424
So I have a table as follows:  ID_STUDENT | ID_CLASS | GRADE -----------------------------    1       |    1     |  90    1       |    2     |  80    2       |    1     |  99    3       |    1     |  80    4       |    1     |  70    5       |    2     |  78    6       |    2     |  90    6       |    3     |  50    7       |    3     |  90   I need to then group, sort and order them to give:  ID_STUDENT | ID_CLASS | GRADE | RANK ------------------------------------     2      |    1     |  99   |  1     1      |    1     |  90   |  2     3      |    1     |  80   |  3     4      |    1     |  70   |  4     6      |    2     |  90   |  1     1      |    2     |  80   |  2     5      |    2     |  78   |  3     7      |    3     |  90   |  1     6      |    3     |  50   |  2   Now I know that you can use a temp variable to rank, , but how do I do it for a grouped set?  Thanks for any insight!
20543
I have a very simple MySQL table where I save highscores. It looks like that:  Id     Name     Score   So far so good. The question is: How do I get what's a users rank? For example, I have a users Name or Id and want to get his rank, where all rows are ordinal ordered descending for the Score.  An Example  Id  Name    Score 1   Ida     100 2   Boo     58 3   Lala    88 4   Bash    102 5   Assem   99   In this very case, Assem's rank would be 3, because he got the 3rd highest score.  The query should return one row, which contains (only) the required Rank.
67853
Is it possible to do a SELECT statement with a predetermined order, ie. selecting IDs 7,2,5,9 and 8 and returning them in that order, based on nothing more than the ID field?  Both these statements return them in the same order:   SELECT id FROM table WHERE id in (7,2,5,9,8)    SELECT id FROM table WHERE id in (8,2,5,9,7)
36179
I am reviewing take aways. I am having trouble  How do I do $342 - 58$?  For the ones column I made the $2$ into a $12$ so I can do $12 - 8 = 4$ but I must take away one tens. So I get $3 - 5$ in tens column but I cant do $3 - 5$. What do I do now do I borrow something else?
25277
I need an aggregate function that MySQL doesn't provide.  I would like it to be in MySQL's flavor of SQL (that is, not in C).  How do I do this?  What I'm stuck on is creating an aggregate function -- the  don't seem to mention how this is done.  Examples of desired usage of a product function:  mysql&gt; select product(col) as a from `table`; +------+ | a    | +------+ |  144 | +------+ 1 row in set (0.00 sec)  mysql&gt; select col, product(col) as a from `table` group by col; +-----+------+ | col | a    | +-----+------+ |   6 |   36 | |   4 |    4 | +-----+------+ 2 rows in set (0.01 sec)
36213
I'm a bit stuck on this. Basically I want to do something like the following SQL query in LINQ to SQL: SELECT f.*  FROM Foo f WHERE f.FooId IN (     SELECT fb.FooId     FROM FooBar fb     WHERE fb.BarId = 1000 )  Any help would be gratefully received.
47503
Tried my best with the title. Ok, earlier while I was on break from work (I have low level math, and want to be more fond of mathematics)  I work retail, and 20% of taxes are taken out, and I am wanting to find out how much I made before 20% is taken out.  So I did some scribbling, and this is without googling so please be gentle. but lets say I make 2000 dollars per paycheck, so to see how much money I get after taxes I do 2000 * .8 which gives me 1600. I can also do 2000 * .2 which gives me 400 and then do 2000 - 400 (imo I don't like this way since I have a extra unnecessary step)  Anyways, I was pondering how to reverse that to have the answer be 2000, and what I did was 1600 * 1.20 (120%) which gave me 1920, and I thought that is odd so I did 1600 * 1.25 and answer was 2000 exact.  My question is why did I have to add extra 5% (25%) to get my answer? I am sure I did something wrong, and I fairly confident the formula I am using is a big no no.  edit; wow thank you all for your detailed answers. I am starting to like mathematics more and more.
2551
Table:   UserId, Value, Date.   I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (Preferably Oracle)  Update: Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that row where that user has the latest date.
39637
What is the simplest SQL query to find the second largest integer value in a specific column?   There are maybe duplicate values in the column.