I have the table ABC with the following data  Id  Name    Date      Execution id  --  ----  ---------  ------------- 1    AA   09SEP2019      11 1    AA   08SEP2019      22 1    AA   07SEP2019      33 2    BB   09SEP2019      44 2    BB   08SEP2019      55 2    BB   07SEP2019      66   And I want to get for every distinct ID in the table its max date. So the result set must be as the following  Id Name     Date     Execution id  -- ----  ---------  ------------- 1   AA   09SEP2019      11 2   BB   09SEP2019      44   The query that returns the result I need  WITH MaxDate as (    SELECT Id,Name,Max(Date) from ABC group by Id,Name ) SELECT view1.*, view2.exection_id from        MaxDate view1,        ABC     view2 WHERE        view1.date=view2.date and        view1.name=view2.name;   I don't like to get the max date for the distinct ID by this way. May be there is another way ? Might be there is more easiest way?
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.
115476
I have a database that containes user data and need to dig up only the entries where two attributes are duplicate between users (surname and social security number). I need all of the attributes for these entries, such as email and firstname.   I first used group by to get every user where those two values are duplicates with this query:  SELECT *  FROM jasenet  GROUP BY hetu, sukunimi  HAVING count(*) &gt;= 2  ORDER BY sukunimi;   This returns all duplicate entries. After some research I tried to use IN operator in order to find out each individual duplicate person with this query:  SELECT *  FROM jasenet  WHERE sukunimi  IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) ORDER BY sukunimi;   This works somewhat because I get each individual with duplicate surname, but in addition to this I need to be able to have the query use the social security number as well (hetu).  I tried this query, but it didn't work:  SELECT *  FROM jasenet  WHERE sukunimi  IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) AND hetu IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) ORDER BY sukunimi;   How can I use two columns returned from a query in another query?
20935
I want to return top 10 records from each section in one query. Can anyone help with how to do it?  Section is one of the columns in the table.  Database is SQL Server 2005. I want to return the top 10 by date entered.  Sections are business, local, and feature.  For one particular date I want only the top (10) business rows (most recent entry), the top (10) local rows, and the top (10) features.
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   :
43671
I have written below inner join query to get the data from my database tables. But  output of this data is coming up with almost 140+ lines. Output is below. I need data in only one row with the most recent value by "TimeSampled"    select DisplayName, ObjectName, CounterName, InstanceName, SampleValue, max(TimeSampled) as TimeSampled from PerformanceDataAllView pdv with (NOLOCK) inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId where ObjectName = 'Processor'  AND TimeSampled &gt;= DATEADD(MINUTE,-5,GETDATE()) AND DisplayName Like 'BLRW1ETST01.jcp.com' AND CounterName = '% Processor Time' GROUP BY DisplayName, ObjectName, CounterName, InstanceName, SampleValue ORDER BY  TimeSampled desc   How I can retrive results with only last Time Sampled row with respect to DisplayName. I have multiple servers (DisplayName) like this to retrieve the data.  Thanks, Ramu Chittiprolu
79002
I have all kinds of views in mysql with employee data.  I have tried to use methods like this - but they simply don't work for views.    I end up getting more of a LEFT JOIN as a result.  Is there a way to merge two views without including duplicates in mysql - I am currently using a 4 view process to duplicate this.
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.
10446
How do you return 1 value per row of the max of several columns:  TableName  [Number, Date1, Date2, Date3, Cost]   I need to return something like this:  [Number, Most_Recent_Date, Cost]   Query?
122494
Consider:  with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day )  select 'Inspections'::text as data_label, count(i.reporting_id) as daily_count, d.day as date_column from days d left join inspection i on i.close_case_date = d.day group by d.day order by d.day   This returns a set that looks like this:  data_label | daily_count | date_column Inspections    1           01/01/13 Inspections    2           01/02/13 Inspections    4           01/04/13 Inspections    8           01/06/13   Note the 1 and 2 day gaps in the record set. I need to generate a set with those values filled in with 0 like so:  data_label | daily_count | date_column Inspections    1           01/01/13 Inspections    2           01/02/13 Inspections    0           01/03/13 Inspections    4           01/04/13 Inspections    0           01/05/13 Inspections    8           01/06/13   How would I do this? I am not much of a Database Developer and am new to Postgres, so I am not sure where to start and can not find anything on how to accomplish this objective.
81977
I'm having trouble understanding how to access each individual result returned from a subquery. The picture shows the code which:   takes fields and objects chosen by a user and assigns them to String variables, the string vars are used to query a dynamic query string, the query string is feed into a database.query and the result is stored in an array I can individually access each of the query results from this array (parent object results),  But I have not been able to individually access the subquery results.   If I feed the subquery results into an array...something like:  SObject[] c = p.get(i).getSObjects(childO);   I still can't get to each individual child record per parent. I can only see all child records per each parent. I need to get to each child record per adult, so that I can roll up results.  Can anyone help?  // These strings will be populated dynamically from a VF page String parentO  = 'Account'; String parentF  = 'Products_Purchased__c'; String childO   = 'Opportunity';     if(childO  == 'Opportunity'){ childO = 'Opportunities'; } String childF   = 'Product_Type__c';  // create dynamic SOQL query string String query = 'SELECT id, (select ' +childF+ ' from ' +childO+') FROM ' +parentO;  // query and store 33 results in variable p SObject[] p = Database.query(query);  for(Integer i = 0; i &lt; p.size(); i++){     system.debug('Parent result from SOQL query: ' +p[i]);     if(p.get(i).getSObjects(childO) &lt;&gt; null){         system.debug('Child result(s) from SOQL query: ' +p.get(i).getSObjects(childO));         system.debug('Child field: ' +c.get(i).get(childF));     } }
58142
I have a database table that contains a list of contacts, some of those contacts might have multiple records, e.g.  CustomerID, CustomerName, Vehicle 1, Dan, Mazda 1, Dan, Suzuki 2, John, Ford 3, Dasha, Volvo 3, Dasha, Ford   Can I write a select query to return the distinct customerID and CustomerName, and a list of vehicles in 1 record? i.e.  1, Dan, Mazda+Suzuki 2, John, Ford 3, Dasha, Volvo+Ford   Thanks
30328
The base problem I'm facing is that I need the latest record for each item.    A little setup... MySQL 5.6.14.  I need to create two views (because MySQL won't let me have a subquery in a view).  My first query sets up the data like this.  select      `inventoryrecords`.`inventoryrecordid` AS `inventoryrecordid`,     `inventoryrecords`.`logicaldeviceid` AS `logicaldeviceid`,     `inventoryrecords`.`passrfid` AS `passrfid`,     `inventoryrecords`.`tagepc` AS `tagepc`,     `inventoryrecords`.`currentstate` AS `currentstate`,     `inventoryrecords`.`statedateutc` AS `statedateutc`,     `inventoryrecords`.`ownerobjectid` AS `ownerobjectid`,     `inventoryrecords`.`ownerobjecttype` AS `ownerobjecttype` from     `inventoryrecords` where     1 order by `inventoryrecords`.`statedateutc` desc   Then I can restrict everything to just the last record for each TagEPC with my "real" query like this.  select      `lastinventoryrecords_step1`.`inventoryrecordid` AS `inventoryrecordid`,     `lastinventoryrecords_step1`.`logicaldeviceid` AS `logicaldeviceid`,     `lastinventoryrecords_step1`.`passrfid` AS `passrfid`,     `lastinventoryrecords_step1`.`tagepc` AS `tagepc`,     `lastinventoryrecords_step1`.`currentstate` AS `currentstate`,     `lastinventoryrecords_step1`.`statedateutc` AS `statedateutc`,     `lastinventoryrecords_step1`.`ownerobjectid` AS `ownerobjectid`,     `lastinventoryrecords_step1`.`ownerobjecttype` AS `ownerobjecttype` from     `lastinventoryrecords_step1` group by `lastinventoryrecords_step1`.`tagepc` order by `lastinventoryrecords_step1`.`statedateutc` desc   When I try to select * from the "real" view, I do not get the data I expect.  However when I run the query with the sub-query in a window I do.  select * from (  lastinventoryrecords_step1 ) group by tagepc order by statedateutc desc   yields incorrect data whereas   select * from (     select          inventoryrecordid,         logicaldeviceid,         passrfid,         tagepc,         currentstate,         statedateutc,         ownerobjectid,         ownerobjecttype     from         inventoryrecords     where         1     order by statedateutc desc ) as t1 group by tagepc order by statedateutc desc   yields the right data.  What in the heck is going on?  What am I missing?
6359
What's the best way to get the last inserted id using sqlite from Java? Google is giving me different answers--some say select the last-insert-rowid; others say call statement.getGeneratedKeys(). What's the best route to take? (I just want to return the id, not use it for other inserts or anything.)
38047
I am trying to put together a query will return sales information across multiple date ranges. I was planning to do something like this:  SELECT count(*) AS thirty_day_sales FROM "MySchema"."MyTable" MT WHERE MT.date &gt; now()::date - interval '30 days' UNION SELECT count(*) AS sixty_day_sales FROM "MySchema"."MyTable" MT WHERE MT.date &gt; now()::date - interval '60 days' UNION SELECT count(*) AS ninety_day_sales FROM "MySchema"."MyTable" MT WHERE MT.date &gt; now()::date - interval '90 days'   Is there another, more efficient way to do this that doesn't use UNION? I'd prefer not to have to repeat the SELECT and WHERE lines over and over, as the actual criteria are a lot longer and move convoluted than this, with multiple tables joined together.
23049
Using standard mysql functions is there a way to write a query that will return a list of days between two dates.  eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values:   2009-01-01   2009-01-02   2009-01-03  2009-01-04   2009-01-05  2009-01-06  2009-01-07  2009-01-08   2009-01-09  2009-01-10  2009-01-11  2009-01-12  2009-01-13   Edit: It appears I have not been clear. I want to GENERATE this list. I have values stored in the database (by datetime) but want them to be aggregated on a left outer join to a list of dates as above (I am expecting null from the right side of some of this join for some days and will handle this).
48494
Can you get the distinct combination of 2 different fields in a database table?  if so, can you provide the SQL example.
52970
SELECT      Id,     CurrencyIsoCode,      ExpirationDate__c,     (SELECT           Id,          Regional_Starting_Price_2__c       FROM           SBQQ__QuoteLine__c)  FROM      SBQQ__Quote__c   WHERE      Id ='quote_Id'