I have a query that will currently return multiple records because the date is slightly different amongst them.  I would like to return the record with earliest date per ID.  Example output:  ID  AMOUNT  DATE 1   10      1 1   20      2 1   30      3 1   1       4 2   34      1 2   234     2 2   234     3 2   34      4 3   3       1 3   3       2 3   23      3 3   20      4   Desired output:  ID  AMOUNT  DATE 1   10      1 2   34      1 3   3       1   Here is my current query:  SELECT DISTINCT th.somefield21 as field21,  th.somefield18 AS field18,  th.somefield17 AS field17,  (c.somefield19 || ' ' || c.somefield20) AS field19,  CASE when th.somefield15 = 'TEST' then (b.apple || ' ' || b.bananna || ' ' || b.peach || ' ' || b.plum || ' ' || b.grape) else a.somefield16 END AS field16,  a.somefield1 AS field1,  a.somefield2 AS field2,  a.somefield3 AS field3,  th.somefield4 AS field4,  s.somefield5 AS field5,  th.somefield6 AS field6,  th.somefield7 AS field7,  th.somefield8 AS field8,  s.somefield9 AS field9,  s.somefield10 AS field10,  s.somefield11 AS field11,  s.somefield12 AS field12,  s.somefield13 AS field13,  th.somefield14 AS field14  FROM schema.therm th,  schema.charlie c,  schema.faux f,  schema.alpha a,  schema.bravo b,  schma.sierra s,  schema.delta d,  schema.golf g,  schema.echo e,  schema.foxtrot ft    WHERE ft.act='submit'       AND ft.tid = th.tid        AND ft.aid = c.aid       AND c.cid = f.cid       AND f.addid = a.addid       AND f.addid = b.addid       AND th.tid = d.tid       AND ft.aid = e.aid      AND e.dsrid = g.dsrid       AND g.dsprid = s.dsprid       AND th.ttype = 'c1'       AND f.add = 'wookie'       AND th.date between to_date('01/01/2001 00:00:00','MM/DD/YYYY HH24:MI:SS') and to_date('10/01/2019 23:59:59','MM/DD/YYYY HH24:MI:SS')       AND s.code = 'han'       AND th.state != 'darth'       AND d.act NOT IN ('peach','pear','cherry','plum')  ORDER BY th.field21;   Which results in multiple records for entries with the same ID.  I tried adding min(th.somefield18 AS field18,) but that resulted in   ORA-00937: not a single-group group function 00937. 00000 -  "not a single-group group function".   When I attempt to add GROUP BY field19 it results in   ORA-00979: not a GROUP BY expression 00979. 00000 -  "not a GROUP BY expression"
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.
20783
I have a table that looks like this:  +----------------------------------------+ |Name                | kode      | jum   | +----------------------------------------+ | aman               |kode1      | 2     | | aman               |kode2      | 1     | | jhon               |kode1      | 4     | | amir               |kode2      | 4     | +--------------------+-----------+-------+   How can I make a view like this with MySQL?                      kode1    kode2     count aman                  2         1        3   jhon                  0         4        4 amir                  0         4        4
67080
I have a table with 5 columnsID,CPT4,CPT4Mod,ItemCharge,Eff_Date.   Deletion Condition:  When rows exists with duplicate CPT4and CPT4Mod field values but different ItemCharge and Eff_Date values, exclude all but the most recent Eff_Date records.  I need a query which can achieve this in two scenarios.   When CPT4Mod is given by the user. When  CPT4Mod is not given by the user.   Later I need to achieve the same using LINQ.
82706
What is the equivalent of varchar(max) in MySQL?
21450
Maybe this question has been asked already (it should be quite simple). I've Googled loads of phrases  (PIVOT, ROWS to COLUMNS, TRANSPOSE &amp;c.) and all I get is  TSQL this, TSQL that and or Oracle this, Oracle that, proprietary function this, proprietary function that well, you get the picture.  I have a simple table structure - if it was any simpler, it'd be a list :-) - i.e. it's two columns.  stock_date       sku ----------       --- 2016-06-01         0 2016-06-02       123 2016-06-03       123 2016-06-04       123 2016-06-06       123 2016-06-07       123 2016-06-03       245 &lt;... &amp;c...&gt;   There may be many thousands of sku's but the date range will be at most a month or so, but can vary, so the code needs to cater for that.   So, I want to make the stock_date field a column.   sku     1   2   3   4   5   6   7 ---     -   -   -   -   -   -   -  123     0   Y   Y   Y   0   Y   Y 245     0   0   Y.... &amp;c.   Ideally, the code would be dynamic and would work with any server (even MySQL).   But if it has to use common table expressions and/or window functions, that's  OK but not proprietary crosstab or pivot functions.
16262
I have a table (t0), in my Postgres DB, with data that looks something like this:  t1_id  t2_id 1      1       2      1 2      1 4      null 4      null 5      null   And I have a query to return my desired results of:  t1_id  t2_id 1      1 4      null 5      null   My query looks something like this:  (   SELECT DISTINCT ON (t2_id) t1_id, t2_id   FROM t0   WHERE t2_id IS NOT NULL ) UNION ALL (   SELECT DISTINCT ON (t1_id) t1_id, t2_id   FROM t0   WHERE t2_id IS NULL )   Is there a faster way to do an operation like this? It's not too bad, but I'm doing it in several places (with joins) and all these repeated queries seems to slow stuff down a bit. Seems like there must be a better way.  Here's the query in fiddle form:
74540
I have a query pattern that must be very common, but I don't know how to write an efficient query for it.  I want to look up the rows of a table that correspond to "the most recent date not after" the rows of another table.  I have a table, inventory say, which represents the inventory I hold on a certain day.  date       | good | quantity ------------------------------ 2013-08-09 | egg  | 5 2013-08-09 | pear | 7 2013-08-02 | egg  | 1 2013-08-02 | pear | 2   and a table, "price" say, which holds the price of a good on a given day  date       | good | price -------------------------- 2013-08-07 | egg  | 120 2013-08-06 | pear | 200 2013-08-01 | egg  | 110 2013-07-30 | pear | 220   How can I efficiently get the "most recent" price for each row of the inventory table, i.e.  date       | pricing date | good | quantity | price ---------------------------------------------------- 2013-08-09 | 2013-08-07   | egg  | 5        | 120 2013-08-09 | 2013-08-06   | pear | 7        | 200 2013-08-02 | 2013-08-01   | egg  | 1        | 110 2013-08-02 | 2013-07-30   | pear | 2        | 220   I know one way of doing this:  select inventory.date, max(price.date) as pricing_date, good from inventory, price where inventory.date &gt;= price.date and inventory.good = price.good group by inventory.date, good   and then join this query again to inventory. For large tables even doing the first query (without joining again to inventory) is very slow.  However, the same problem is quickly solved if I simply use my programming language to issue one max(price.date) ... where price.date &lt;= date_of_interest ... order by price.date desc limit 1 query for each date_of_interest from the inventory table, so I know there is no computational impediment.  I would, however, prefer to solve the whole problem with a single SQL query, because it would allow me to do further SQL processing on the result of the query.  Is there a standard way to do this efficiently? It feels like it must come up often and that there should be a way to write a fast query for it.  I'm using Postgres, but an SQL-generic answer would be appreciated.
55154
I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry.  I use recent versions of PostgreSQL and MySQL. I have 2 tables:  CREATE TABLE authors (     id INT,     name VARCHAR )  CREATE TABLE posts (     id INT,     author_id INT,     text VARCHAR,     date DATE )   I need to select one most recent post for each author. Thanks!  UPDATE  Thanks, both links provide answers to my question with some exception. All the following queries give the same result (which one is the most efficient btw?) The issue is when there is more than one post from the same author with the same date. Then the returned result set contains all such posts. How should I modify these queries to return exactly one post per author?  SELECT p1.* FROM posts p1 LEFT JOIN posts p2 ON p1.author_id = p2.author_id AND p1.date &lt; p2.date WHERE p2.author_id IS NULL ORDER BY p1.author_id;  SELECT p1.*  FROM posts p1 INNER JOIN (   SELECT author_id, MAX(date) AS max_date   FROM posts   GROUP BY author_id) p2   ON p1.author_id = p2.author_id AND p1.date = p2.max_date ORDER BY p1.author_id;  SELECT * FROM posts p1 WHERE date = (SELECT MAX(p2.date)               FROM posts p2               WHERE p1.author_id = p2.author_id) ORDER BY author_id;  SELECT * FROM (     SELECT author_id, MAX(date) date     FROM posts GROUP BY author_id ) p1 INNER JOIN posts p2 USING (author_id, date) ORDER BY author_id;
107229
Lately, I have been trying to figure out the most generic query for getting tracking data into one table. As suggested in the , I have been using this join:  ... FROM _Sent s LEFT JOIN _Job as j ON s.JobID = j.JobID  LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1 LEFT JOIN _Click c ON s.JobID = c.JobID  and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1 LEFT JOIN _bounce b ON s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and b.IsUnique = 1   My results show the same email send, with the same JobID, sent exactly at the same time, to the same subscriber, but with different batch numbers. On top of that, only the first record has an open date:    According to documentation, BatchID   is "The batch ID number for any batches used in the send" - but what does it mean, really? And how should the  BatchID be treated in queries?   Thanks!
61628
I would like to convert several range of columns into rows, and keeping the association to the original col/row source  Sample source sheet  --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc...365 days | --------------------------------------------------- | name 1  |    25    |   30     |    5     | etc...|  --------------------------------------------------- | name 2  |    2     |          |    4     | etc...| --------------------------------------------------- | etc ... 100 rows   Expected Results  --------------------------------------------------- | Data 1 |   Date      |  Value   |  --------------------------------------------------- | name 1  |  1/1/2018   |   25     |    --------------------------------------------------- | name 1  |  1/2/2018   |   30     |    --------------------------------------------------- | name 1  |  1/3/2018   |   5      |    --------------------------------------------------- | name 1  |  etc.. 365 days --------------------------------------------------- | name 2  |  1/1/2018   |   2      |    --------------------------------------------------- | name 2  |  1/2/2018   |          |    --------------------------------------------------- | name 2  |  1/3/2018   |   4      |    --------------------------------------------------- | name 2  |  etc.. 365 days --------------------------------------------------- | etc ... for each change of name pull all the col dates/values   Current results  --------------------------------------------------- | Data 1 |   Date      |  Value   |  --------------------------------------------------- | name 1  |  1/1/2018   |   25     |    --------------------------------------------------- |         |  1/2/2018   |   30     |    --------------------------------------------------- |         |  1/3/2018   |   5      |    --------------------------------------------------- |         |  etc.. 365 days                      I have to copy down to bring in "name 1" for each date  --------------------------------------------------- | name 2  |  1/1/2018   |   2      |             now update the formula to bring in the second item for the sequence of dates --------------------------------------------------- |         |  1/2/2018   |          |    --------------------------------------------------- |         |  1/3/2018   |   4      |    --------------------------------------------------- |         |  etc.. 365 days --------------------------------------------------- | etc ...     =Sheet1!A:A I can bring in the name from the source list, but I have to copy it down for the number of dates.  How can I have it match the number of dates?  and be dynamic to update as dates/values are added Edit: trying to use split and rept to count the number of times to repeat, then transpose.. =transpose(rept(split(arrayformula(concatenate(Sheet1!A:A&amp;"1")),"1",True, True),COUNTA(Sheet1!I1:ZZ1)))  =TRANSPOSE(Sheet1!$C$1:$Z$1) I can use transpose to get the dates/values, but it's fixed range.  How can I make it dynamic to import dates as they are added?
34912
Let's say I have Table A: BookingsPerPerson  Person_Id    ArrivalDate    DepartureDate 123456       2012-01-01     2012-01-04 213415       2012-01-02     2012-01-07   What I need to achieve with a view is the following:  Person_Id    ArrivalDate    DepartureDate    Jan-01    Jan-02    Jan-03    Jan-04    Jan-05    Jan-06    Jan-07 123456       2012-01-01     2012-01-04       1         1         1         1 213415       2012-01-02     2012-01-07                 1         1         1         1         1         1   The system is for events, so each hotel booking could take anything between 1 to 15 days but no more than that. Any ideas would be very much appreciated.
123183
I have the following query that sort of works, but I find it to be kludgy and inefficient.  Is there a better way to write this query?  I know this is a bit of an odd query, else I would probably have found some advice when searching, but it is necessary for our business logic.  We've been doing this in Hibernate queries and using software to output the file but we're needing to improve performance drastically and that means using raw sql.  The output of this query is to be used to partially populate a helper table that we query to figure out which of our 40-odd product tables a particular item is contained in.  Could the following query be replicated using something like GROUP_CONCAT in reverse? Note that the second and third column must be repeated on each line.  The fields all contain the same type of data.  SELECT 'Pn', 'Mfg', 'TableId', 'SourceColumn', 'OtherData' SELECT PN1, Mfg, '26', 'PN1', OtherData FROM Table  UNION ALL SELECT PN2, Mfg, '26', 'PN2', OtherData FROM Table UNION ALL SELECT PN3, Mfg, '26', 'PN3', OtherData FROM Table UNION ALL SELECT PN4, Mfg, '26', 'PN4', OtherData  INTO OUTFILE 'XXX.dat' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM Table d;   Input looks like the following table:       |  PN1  |  PN2  |  PN3    |  PN4    |  MFG  |  TableId  |  OtherData  |     -----------------------------------------------------------------------     |  asdf |  abde |  12354  |  asdfc  |  2    |  26       |  0.2456     |     |  nupo |  1354 |  null   |  null   |  2    |  26       |  1.53       |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |   I want the output .dat file to look like this:       "Pn","Mfg","TableId","SourceColumn","OtherData"     "asdf",2,26,"PN1",0.2456     "abde",2,26,"PN2",0.2456     "12354",2,26,"PN3",0.2456     "asdfc",2,26,"PN4",0.2456     "nupo",2,26,"PN1",1.53     "1354",2,26,"PN2",1.53     ...     ...     ...   If I take the INTO OUTFILE clause out, I get an output that looks like this:       |  Pn      |  Mfg     |  TableId    |  SourceColumn  | OtherData  |     -----------------------------------------------------------------     |  "Pn"    |  {blob}  |  "TableId"  |  "PN"          |  {blob}    |     |  "asdf"  |  {blob}  |  "PN1"      |  "PN1"         |  {blob}    |     |  "abde"  |  {blob}  |  "PN2"      |  "PN2"         |  {blob}    |     |  "12354" |  {blob}  |  "PN3"      |  "PN3"         |  {blob}    |     |  "asdfc" |  {blob}  |  "PN4"      |  "PN4"         |  {blob}    |     |  "nupo"  |  {blob}  |  "PN1"      |  "PN1"         |  {blob}    |     |  "1354"  |  {blob}  |  "PN2"      |  "PN2"         |  {blob}    |     |  ...     |  ...     |  ...        |   ...          |   ...      |     |  ...     |  ...     |  ...        |   ...          |   ...      |     |  ...     |  ...     |  ...        |   ...          |   ...      |
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
82651
I have an output of a query as follows:  bid  | code | amount        | ----------------------------- 2915 | BF   |   -10700.00   | 2915 | YQ   |   -300.00     | 2915 | YR   |   0.00        | 2915 | YM   |   0.00        | 2915 | WO   |   -153.00     | 2915 | IN   |   -329.00     | 2915 | K3   |   0.00        | 2915 | CUTE |   -50.00      | -----------------------------   I need to convert the values in code column to individual columns with column name as corresponding code and amount shall be the value.  bid  | BF       | YQ     | ... | CUTE  | ---------------------------------------- 2915 | -10700.00| -300.00| ... | -50.00|   The issue is that the result of my initial query has dynamic codes and hence need to generate columns accordingly.  Already tried the following logic but couldn't achieve the goal:   , ,   All the above links use some expression or the other to generate the columns.   My original resulting rows are result of simple select on multiple joined tables, and has no calculation or expression scope.  Kindly suggest some pointer or hint on how to achieve this.  Many thanks in advance.
45100
I have a simple mysql table which I use as a server booking system. A user can log in to a server, if he has a valid booking for that server. A sample data might look like this:  +----------+----------------+---------------------+---------------------+ | server   | user           | start               | end                 | +----------+----------------+---------------------+---------------------+ | server01 | Alice,Bob      | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 | | server02 | Carlos         | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 | | server03 | Dan,Erin,Frank | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 | +----------+----------------+---------------------+---------------------+   To check whether user Carlos has a booking for server02, I use the following SQL query:  SELECT 0, id FROM booking WHERE user = 'Carlos' AND (( start &lt; now() and end &gt; now() ) AND server = 'server02') ;   But this query does not work if there are multiple (comma-separated) users. How can I check if Alice has booking for server01 ?
61610
I have a query which selects a max value over all the values of in a datetime column (dtimesent).  select dtimesent, max(ResponseTime) AS maxtime FROM allrequests  where Request like '%Requestsa%' group by dtimesent   I need to get the value of the request colmn for each max response time in each group by. Is there any easy way to do this?  There can be multiple rows per dtimesent value so I can't just include the request column in the group by,  Many thanks,
2888
I'm building a quick csv from a mysql table with a query like:  select DATE(date),count(date) from table group by DATE(date) order by date asc;   and just dumping them to a file in perl over a:  while(my($date,$sum) = $sth-&gt;fetchrow) {     print CSV "$date,$sum\n" }   There are date gaps in the data, though:  | 2008-08-05 |           4 |  | 2008-08-07 |          23 |    I would like to pad the data to fill in the missing days with zero-count entries to end up with:  | 2008-08-05 |           4 |  | 2008-08-06 |           0 |  | 2008-08-07 |          23 |    I slapped together a really awkward (and almost certainly buggy) workaround with an array of days-per-month and some math, but there has to be something more straightforward either on the mysql or perl side.   Any genius ideas/slaps in the face for why me am being so dumb?    I ended up going with a stored procedure which generated a temp table for the date range in question for a couple of reasons:   I know the date range I'll be looking for every time The server in question unfortunately was not one that I can install perl modules on atm, and the state of it was decrepit enough that it didn't have anything remotely Date::-y installed   The perl Date/DateTime-iterating answers were also very good, I wish I could select multiple answers!