I have a table  M  price1  name             price2 -------------------------------- 88      astérix          80 75      cetautomatix     70 95      panoramix        90 20      assurancetourix  30 105     NULL             NULL   And I want to get the max of price2 with corresponding name and price1  So the result I want would be  price1  name             price2 -------------------------------- 95      panoramix        90   I know that a lot of pretty similar question were asked here and I achieved to find this solution.  SELECT m.price1, m.nom, m.price2 FROM M m WHERE m.price2 = (SELECT MAX(m.price2) FROM M m);   However I'd like to find a solution where I need only one request (Just one SELECT, not two)  So I tried things like that   SELECT m.price1, m.nom, MAX(m.price2) FROM M m Group by m.nom, m.price1   But it doesn't work like i want.  So if someone know a way to do it in only one request it would be really helpfull!  Thanks
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.
29513
Ref :   I got answer (thanks coytech ) but i need one more column in its :  as per :  id | mid | pid | owgh | nwgh | 1    3      12    1.5    0.6 2    3      12    1.5    0.3 3    3      14    0.6    0.4 4    3      15    1.2    1.1 5    4      16    1.5    1.0 6    4      17    2.4    1.2  7    3      19    3.0    1.4   i got answer      Select mid ,          COUNT(distinct pid) as cpid  ,          SUM(nwgh) as totalnwgh  from test  GROUP BY mid   sqlfiddle :   mid  cpid       totalnwgh 3      4          3.8 4      2          2.2    But above i need one more column that's as below : totowgh  mid cpid        totalnwgh  totowgh 3      4          3.8        6.3 (DISTINCT value as per pid column) 4      2          2.2        3.9   where totowgh = 6.3 come by DISTINCT value of owgh column  as per distinct pid column  mid = 3 has count 5 but distinct pid  = 4 for mid=3 same way "distinct" owgh = 6.3 for mid=3 and distinct pid.  As pid=12 , column owgh value 1.5 is twice ...hence its is count 1 time hence,  1.5 + 0.6 + 1.2 + 3 = 6.3 ( please not this is as per DISTINCT value of pid )  Please note : i need distinct owgh value as per distinct pid or group by pid .. because if i replace value of owgh 0.6 with 1.5 then it will be 5.7 instead of 7.2 but value of owgh 0.6 belong to pid = 14 and not pid = 12 hence totalcount of owgh change ...but i need is 7.2  SEE WHAT I MEANS   :
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.
115910
When I view the result of a SELECT with MySQL Workbench, it's correct with one single \  max@host 10:13:58: ~$ mysql -h db-master.domain.local -uuser -ppw db -e ' &gt;                 SELECT &gt;                 DISTINCT i.filesourceregexp &gt;                 FROM db.ImportLogFiles i'  +------------------------------------------------+ | filesourceregexp                               | +------------------------------------------------+ | ^[0-9]{8}_1062355673_merge_google_pbn\.csv$    | | ^[0-9]{8}_8026062435_merge_google_pbn\.csv$    | | ^[0-9]{8}_1062355673_store_visits_report\.csv$ | +------------------------------------------------+  max@host 10:14:10: ~$ mysql -h db-master.domain.local -uuser -ppw db -e '                 SELECT                 DISTINCT i.filesourceregexp                 FROM db.ImportLogFiles i' | tail -n +2 ^[0-9]{8}_1062355673_merge_google_pbn\\.csv$ ^[0-9]{8}_8026062435_merge_google_pbn\\.csv$ ^[0-9]{8}_1062355673_store_visits_report\\.csv$ max@host 10:14:19: ~$    I have these options in my.cnf:  [client]  host = db-master  user = user  password = pass  default-character-set=utf8   Why does piping the result through tail change the output/string? (note the double \).
9465
I have a table to store StudentID, SubjectID, ObtainedMarks. Now I have to create a pivot table to show studentID and obtainedMarks in different subject. Number of subject may differ. I could generate pivot table for fixed subjectID like    SELECT * FROM (SELECT StudentID, SubjectID, ObtainedMarks from [Exam].[ObtainedMarkEntry]) as InvoiceResult PIVOT (     sum(ObtainedMarks) for [SubjectID] in  ([1],[2],[3],[4]) )as finalResult   This works but problem is 1,2,3 is not static it should come from same table   So I did something like   declare @minvalue int, @maxvalue int declare @myArray varchar(max) set @MyArray=''  select RowID=IDENTITY(int, 1,1), SubjectID into #tempSubject from [Exam].[ObtainedMarkEntry] select * from #tempSubject set @minvalue = (select min(RowID) from #tempSubject) set @maxvalue = (select max(RowID) from #tempSubject) while (@minvalue&lt;=@maxvalue) begin     declare @sid nvarchar(10)     set @sid=(select SubjectID from #tempSubject where RowID=@minvalue)     set @myArray= @myArray + '[' + @sid + '], '      set @minvalue= @minvalue+1 end set @myArray= SUBSTRING(@myArray,0,len(@myArray)) drop table #tempSubject  declare @q as nvarchar(max) SELECT * FROM (SELECT StudentID, SubjectID, ObtainedMarks  from [Exam].[ObtainedMarkEntry]) as InvoiceResult PIVOT (     sum(ObtainedMarks) for [SubjectID] in  (@myArray) )as finalResult   Now I am getting error at in(@myArray) can't I assign variable in this section.
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?
89546
I'm trying to create a new field based in another (sum field)  SELECT sum(price) AS old_price,    old_price+100 AS total_price FROM sales   This returns Unknown column 'old_price' in 'field list'  sum is just a example, my real issue is to use with a select inside this field  What am I doing wrong?  Thanks.  --edit I've used variables to reuse the alias, updated code:  SELECT @old_price := sum(price) AS old_price,    @old_price+100 AS total_price FROM sales   If you think that it is not a good practice, tell me.
81401
I have an array of doubles and I want the index of the highest value. These are the solutions that I've come up with so far but I think that there must be a more elegant solution. Ideas?  double[] score = new double[] { 12.2, 13.3, 5, 17.2, 2.2, 4.5 }; int topScoreIndex = score.Select((item, indx) =&gt; new {Item = item, Index = indx}).OrderByDescending(x =&gt; x.Item).Select(x =&gt; x.Index).First();  topScoreIndex = score.Select((item, indx) =&gt; new {Item = item, Index = indx}).OrderBy(x =&gt; x.Item).Select(x =&gt; x.Index).Last();  double maxVal = score.Max(); topScoreIndex = score.Select((item, indx) =&gt; new {Item = item, Index = indx}).Where(x =&gt; x.Item == maxVal).Select(x =&gt; x.Index).Single();
33958
I have a table that looks like this (oversimplified case of the actual case to make my point)  CREATE TABLE sales (    id int,    currency VARCHAR(5),    price int    shop_id int );   Now I need to make a query that will give me something like this table, grouping by shops yet giving sum of sales per currency My problem is when I make currency column non aggregate it fails because my query becomes non standard. When I add currency to group by then each currency creates new row, something I don't want. I have run out of option and would appreciate any help or pointer to the right direction.  I use MySQL but would appreciate if I would get the solution that works with more databases (PostGreSQL being at the top of the list after MySQL)  Query that works with undesired results  SELECT   shop_id,           (CASE WHEN currency= "GBP" THEN SUM(price) ELSE 0 END) AS POUND,           (CASE WHEN currency= "USD" THEN SUM(price) ELSE 0 END) AS USDOLLAR GROUP BY shop_id, currency   The data that should be produced by the query  shop_id     |   POUND   |   USDOLLAR     -----------------------------------------     1       |   400     |   300 -----------------------------------------     2       |   250     |   100 -----------------------------------------     3       |   400     |   100 -----------------------------------------
63506
This is a part of my .  I have the below logic to split a string value from a column in table tblProjects as below:  SELECT  @Docs = Documents FROM tblProjects WHERE ID = @ID  SET @Docs = @Docs + '$' SET @pos = 0 SET @len = 0  WHILE CHARINDEX('$', @Docs, @pos + 1)&gt;0 BEGIN   SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos   SET @value = SUBSTRING(@Docs, @pos, @len)    IF CHARINDEX('|', @value) &gt;0   BEGIN     SELECT  @type = SUBSTRING(@value, 1, CHARINDEX('|', @value) - 1),             @name = SUBSTRING(@value, CHARINDEX('|', @value) + 1, 200)     INSERT INTO tblDocuments VALUES(@ID, @value, @type, @name)   END       SET @pos = CHARINDEX('$', @Docs, @pos + @len) +1 END   A sample input would be   insert into tblProjects(ID, Documents)  values('P_1', 'gdgfg|jkkgk$bkgkkj|kjgkgk$vjffj|Khkgjhg$jkgkvghv|kbkghhkgk');   I need to split the Documents string and insert the values into a separate table. Values delimited by | should go into separate columns of the same row, whereas values for a particular row are delimited by $.   Is there a single query method to complete this operation, for all rows in the table, in the minimum possible time?
51458
If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?  If the first SELECT statement is selecting into a temp table and the second SELECT statement is then inserting into that same temp table, does that prevent it from being possible for the two statements to be ran in parallel?  (I'm guessing the answers are yes and yes :).
113329
i have a table like this  +--------+------------+-----------+ | id     | volume     | price     | +--------+------------+-----------+ |      1 | 1.0000     |  14250.00 | |      2 | 0.2500     |  14251.10 | |      3 | 0.2500     |  14250.00 | |      4 | 0.0050     |  14200.00 | |      5 | 0.5000     |  14255.01 | +--------+------------+-----------+   Simply if volume 1.7500, how to select like bellow by ordering(highest price)  +--------+------------+-----------+ | id     | volume     | price     | +--------+------------+-----------+ |      5 | 0.5000     |  14255.01 | |      2 | 0.2500     |  14251.10 | |      1 | 1.0000     |  14250.00 | +--------+------------+-----------+   And if volume 1.5000 it also select the least amounts of records needed to calculate to the input volume  +--------+------------+-----------+ | id     | volume     | price     | +--------+------------+-----------+ |      5 | 0.5000     |  14255.01 | |      1 | 1.0000     |  14250.00 | +--------+------------+-----------+   some guys say i cant do this with sql
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!
111744
Say I have a table called User_FriendList, which has the following characteristics:  CREATE TABLE User_FriendList (     ID ...,     User_ID...,     FriendList_IDs...,     CONSTRAINT User_Friendlist_PK PRIMARY KEY (ID) );   And let us suppose that said table holds the following data:    +----+---------+---------------------------+  | ID | User_ID | Friendlist_IDs            |  +----+---------+---------------------------+  | 1  | 102     | 2:15:66:35:26:17:         |  +----+---------+---------------------------+  | 2  | 114     | 1:12:63:33:24:16:102      |  +----+---------+---------------------------+  | 3  | 117     | 6:24:52:61:23:90:97:118   |  +----+---------+---------------------------+   Note: The “:” (colon) is the delimiter when exploding in PHP into an array.  Questions  So:   Is this a convenient way to “store” the IDs of a FriendList? Or, instead, should I have individual rows with only one single FriendId value in each of them and, when I need to retrieve all the rows of a given list, simply perform a query like SELECT * FROM UserFriendList WHERE UserId = 1?
1869
My table is:     id home datetime player resource     1 10 04/03/2009 john 399   2 11 04/03/2009 juliet 244   5 12 04/03/2009 borat 555   3 10 03/03/2009 john 300   4 11 03/03/2009 juliet 200   6 12 03/03/2009 borat 500   7 13 24/12/2008 borat 600   8 13 01/01/2009 borat 700     I need to select each distinct home holding the maximum value of datetime. Result would be:     id home datetime player resource     1 10 04/03/2009 john 399   2 11 04/03/2009 juliet 244   5 12 04/03/2009 borat 555   8 13 01/01/2009 borat 700     I have tried: -- 1 ..by the MySQL manual:   SELECT DISTINCT   home,   id,   datetime AS dt,   player,   resource FROM topten t1 WHERE datetime = (SELECT   MAX(t2.datetime) FROM topten t2 GROUP BY home) GROUP BY datetime ORDER BY datetime DESC  Doesn't work. Result-set has 130 rows although database holds 187. Result includes some duplicates of home. -- 2 ..join  SELECT   s1.id,   s1.home,   s1.datetime,   s1.player,   s1.resource FROM topten s1 JOIN (SELECT   id,   MAX(datetime) AS dt FROM topten GROUP BY id) AS s2   ON s1.id = s2.id ORDER BY datetime   Nope. Gives all the records. -- 3 ..something exotic:   With various results.
52799
In Sqlite 3 I'm trying to figure out how to select rows based on a minimum value.  I think that I'm limited by not knowing enough of the related terminology to effectively search google.  The table looks like:  num         text        num2       ----------  ----------  ---------- 0           a           1          0           a           2          1           a           3          1           b           4            I want to get the rows where num2 is 1, 2, and 4.  I want to do the selection based on the minimum value of num for each unique value of the text column.  So, for text = 'a', the minimum value of numis 0, so I want rows 1 and 2.  For text = 'b', the minimum value of num is 1, so I want row 4.  Using various combination of group by, I'm able to get either rows 1 and 2 or rows 1 and 4.  I feel like I'm missing a SQL component that would do what I want, but I haven't been able to figure out what it could be.  What is the proper way to do this type of query?  Possible Solution  I've found a way to do this.  I'm not reputable enough to answer my own question, so I'm doing the update here.  I'm not sure if it is always correct or what the efficiency is like.  Any comments are welcome.  I used a compound select statement, where one query finds the minimum value of num for each unique value of text:  sqlite&gt; select num, text from t group by text having num = min( num ); num         text       ----------  ---------- 0           a          1           b            Then I joined this with the full table to get all rows matching these two columns.  sqlite&gt; with u as       ( select num, text from t group by text having num = min( num ) )         select t.* from t join u on t.num = u.num and t.text = u.text; num         text        num2       ----------  ----------  ---------- 0           a           1          0           a           2          1           b           4
26778
How can I pivot the following SELECT statement:           SELECT sc.CID,                 sc.CodeName     as OverviewText,                 scRAG.CodeName  as RAGStatusText             FROM StatusCode      sc LEFT OUTER JOIN ProjectOverview po               ON sc.CID          = po.ProjectOverviewCID              AND po.ProjectId    = 180 LEFT OUTER JOIN StatusCode      scRAG               ON po.RAGStatusCID = scRAG.CID           WHERE sc.SCID         = 18   Such statement yields the result set that follows:  CID OverviewText    RAGStatusText --- ------------    ------------- 153 Cost            Green 154 Requirements    Yellow 155 Schedule        NULL 156 Technical       NULL 157 Testing         NULL   But, I want it to return one single row with 10 values, as shown bellow:   ----------------------------------------------------------------------------------------------  | Cost | Green | Requirements | Yellow | Schedule | NULL | Technical | NULL | Testing | NULL |  ----------------------------------------------------------------------------------------------   Can I pivot on CID?