I've following table:  PERSON_ID   EFFECTIVE_END_DATE      ASSIGNMENT_ID   FULL_NAME 33151       2013-08-04 00:00:00.0   33885           Test, C 33151       2013-10-04 00:00:00.0   33885           Test, C 33151       2015-02-19 00:00:00.0   33885           Test, C 33151       2013-08-04 00:00:00.0   33885           Test, C 33151       2013-10-04 00:00:00.0   33885           Test, C 33151       2015-02-19 00:00:00.0   33885           Test, C   Here PERSON_ID is same I want to select row with maximum effective end date without using group by.   Can some one help me ?
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.
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?
8230
I often need to select a number of rows from each group in a result set.  For example, I might want to list the 'n' highest or lowest recent order values per customer.  In more complex cases, the number of rows to list might vary per group (defined by an attribute of the grouping/parent record). This part is definitely optional/for extra credit and not intended to dissuade people from answering.  What are the main options for solving these types of problems in SQL Server 2005 and later? What are the main advantages and disadvantages of each method?  AdventureWorks examples (for clarity, optional)   List the five most recent recent transaction dates and IDs from the TransactionHistory table, for each product that starts with a letter from M to R inclusive. Same again, but with n history lines per product, where n is five times the DaysToManufacture Product attribute. Same, for the special case where exactly one history line per product is required (the single most recent entry by TransactionDate, tie-break on TransactionID.
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.
85566
I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales that are unique based on day and price will get updated to an active status.  So I'm thinking:  UPDATE sales SET status = 'ACTIVE' WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)              FROM sales              HAVING count = 1)   But my brain hurts going any farther than that.
9742
I just need advice on how I am going to optimize my database.  Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN   I have that format of tables on my database. Each tables has 30-days of records with hundred thousands of records each data. Uploading of data to database is every morning. The data of yesterday will be uploaded.  The most common queries to run is grouped by Date or Date and Area or Date and Area and Block. Now, in order to make queries faster, I found out using indexes. I used indexes before. But that was when I am using a primary key. In this table, I didn't use primary key because it is totally unnecessary.   Now, I am really confused on how I am going to optimize this. Based on  on Column Considerations part, clustered index is not a good choice on Columns that undergo frequent changes. I am really confused.  Please help me if I need to use clustered index here or just non clustered or both.
2625
What is the best way to remove duplicate rows from a fairly large SQL Server table (i.e. 300,000+ rows)?  The rows, of course, will not be perfect duplicates because of the existence of the RowID identity field.  MyTable  RowID int not null identity(1,1) primary key, Col1 varchar(20) not null, Col2 varchar(2048) not null, Col3 tinyint not null
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
61017
I have two tables:   things:  +------+----------------+------------+ | id   | current_status | created_at | +------+----------------+------------+ | 7770 | active         | 2016-08-09 | +------+----------------+------------+  thing_status_tract:  +----------+-------------+-----------+---------------------+ | thing_id | status_from | status_to | changed_at          | +----------+-------------+-----------+---------------------+ |     7770 | incomplete  | inactive  | 2016-08-09 16:26:22 | |     7770 | inactive    | active    | 2016-08-10 12:31:04 | +----------+-------------+-----------+---------------------+    I need data in the following form. This table has a status and its corresponding start and end timestamp for a particular thing_id:  +----------+-------------+---------------------+---------------------+ | thing_id | status      | status_start_date   | status_end_date     | +----------+-------------+---------------------+---------------------+ |     7770 | incomplete  | 2016-08-09 00:00:00 | 2016-08-09 16:26:22 | |     7770 | inactive    | 2016-08-09 16:26:22 | 2016-08-10 12:31:04 | |     7770 | active      | 2016-08-10 12:31:04 | now()               | +----------+-------------+---------------------+---------------------+   How to do that with a SQL query?
62227
I need to get all the dates present in the date range using SQL Server 2005
37248
When removing duplicate rows, using the below query from  tutorial, how would I go about forcing which of the found duplicates to remove?  DELETE FROM dbo.ATTENDANCE      WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _ FROM dbo.ATTENDANCE      GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)    And this works great. The reason why I am using this one is because the only unique id available is one in the IDENTITY column. And to determine if there are duplicate rows I have to look at a combination of multiple columns.    But if I have a set of duplicate rows how do I / how does SQL Server decide which to remove? And how would I force it to remove all but the one with the highest IDENTITY value?  EMPLOYEE_ID     ATTENDANCE_DATE     AUTOID A001            2011-01-01          1 A001            2011-01-01          2   If I would run the query now it happens to remove the second one, with AUTOID 2. But I am trying to remove all but this one (because this is the one latest added).
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!
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 ?
51700
MySQL column > sdate, edate ( its 2 column).  sdate is start date for project starting and edate is end date for project ending.  so i need to make search between them..  &lt;strong&gt;Search&lt;/strong&gt;&lt;br /&gt; &lt;form method="post" action="search.php"&gt;   Start Report Date : &lt;input type="text" name="sdate" /&gt;   End Report Date : &lt;input type="text" name="edate" /&gt;   &lt;input type="submit" name="Submit" value="Search" /&gt; &lt;/form&gt;  This is example data in mysql sdate             Project Name      edate 22 December 2008  project 1         23 December 2008 25 December 2008  project 2         26 December 2008 24 December 2008  project 3         27 December 2008 1  January 2008   project 4         20 January 2008 10 December 2008  project 5         12 December 2008   so let say a user entered sdate ( eg, 22 December 2008 ) and edate ( eg, 30 December 2008 ).  It should display  22 December 2008  project 1         23 December 2008 25 December 2008  project 2         26 December 2008 24 December 2008  project 3         27 December 2008   So i need a php code sql query which should display entries lies between those 2 dates..  Please help me..   Thanks very much..
32424
I have a table with the following structure:  CREATE TABLE `Rings` (     ID_RingType CHAR(2),     Number MEDIUMINT UNSIGNED,     ID_User INT(11) );   and with data:  INSERT INTO `Rings` VALUES   ('AA',1,1),   ('AA',2,1),   ('AA',3,1),   ('AA',11,1),   ('AA',12,1),   ('AA',13,1),   ('AA',14,1),   ('AA',15,1),   ('AB',16,1),   ('AB',17,1),   ('AB',18,1),   ('AB',19,1),   ('AB',20,2),   ('AB',21,2),   ('AB',22,2);   I wish to group the data based on ID_User and ID_RingType and for each contiguous range of numbers list the MIN and MAX.  The results should look like:  ID_User | ID_RingType | MIN  | MAX 1       | 'AA'        | 1    | 3 1       | 'AA'        | 11   | 15 1       | 'AB'        | 16   | 19 2       | 'AB'        | 20   | 22   I went through several posts on this topic but was not able to tweak them to fit my data.  Any help would be appreciated.
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!
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   :