logo
down
shadow

Microsoft SQL Server : return only the rows with the most recent date for each unique ID


Microsoft SQL Server : return only the rows with the most recent date for each unique ID

By : user2174031
Date : October 19 2020, 08:10 AM
To fix the issue you can do You are close to the solution. You just need a few changes in your correlated subquery :
code :
SELECT 
    t.DeviceID, 
    t.AVSolutionName,
    t.DefinitionsUpToDate, 
    t.ScanningEnabled,
    t.Expired,
    t.ScanTime
FROM dbo.fact_AVSecurity AS t
WHERE t.ScanTime =
    (SELECT MAX(ScanTime) AS LastScan
      FROM dbo.fact_AVSecurity AS Avs
      WHERE deviceID = t.deviceID
      GROUP BY DeviceID
    )


Share : facebook icon twitter icon
SQLite: Return the most recent 'n' rows per unique value of column 'y'

SQLite: Return the most recent 'n' rows per unique value of column 'y'


By : Chung Wen-Wei
Date : March 29 2020, 07:55 AM
I hope this helps . I have a table with the following schema: , You could use a correlated subquery. For example, for N = 5 :
code :
select  *
from    YourTable yt1
where   5 <
        (
        select  count(*)
        from    YourTable yt2
        where   yt2.thread_id = yt1.thread_id
                and yt2.msg_id < yt1.thread_id
        )
2 rows having unique field and recent date

2 rows having unique field and recent date


By : kranti kumar charu
Date : March 29 2020, 07:55 AM
should help you out http://sqlfiddle.com/#!2/f37963/9
code :
SELECT t1.* FROM temp t1
      JOIN (SELECT question_id, MAX(`date`) as `date` FROM temp GROUP BY topic_id) t2
        ON t1.question_id= t2.question_id AND t1.`date`= t2.`date`;
Select query where rows with same ID only return row with most recent date field

Select query where rows with same ID only return row with most recent date field


By : Roy Rodman
Date : March 29 2020, 07:55 AM
like below fixes the issue You can use a subquery to select only the maximum date in table2 for each ID:
code :
SELECT a.ID, a.[zip code], b.date
FROM table1 a   
LEFT JOIN (SELECT ID, MAX(date) AS date
           FROM table2
           GROUP BY ID) b ON a.ID = b.ID  
WHERE a.[Zip Code] = '78701' 
ORDER BY a.[zip code] ASC
SQL: Return ID for the most recent date for unique part

SQL: Return ID for the most recent date for unique part


By : John Dere
Date : March 29 2020, 07:55 AM
may help you . This query will number the maximum dates as '1', because of partition...order by quote_date desc clause.
code :
select Quote, Part_number, Quote_date,
       rank() over (partition by part_number 
                    order by quote_date desc) as date_order
from sample
select Quote, Part_number, Quote_date
from 
    (select Quote, Part_number, Quote_date,
            rank() over (partition by part_number 
                         order by quote_date desc) as date_order
    from sample) t1
where date_order = 1;
select Part_Number, max(Quote_Date) as max_quote_date
from sample
group by Part_number;
select s.Quote, s.Part_Number, s.Quote_Date
from sample s
inner join (select Part_Number, max(Quote_Date) max_quote_date
            from sample
            group by Part_number) t1
        on s.Part_Number = t1.Part_number
       and s.Quote_Date = t1.max_quote_date
order by Part_Number;
Select rows based on most recent date including unique information

Select rows based on most recent date including unique information


By : Abecc Cacer Cur
Date : March 29 2020, 07:55 AM
Does that help my suggestion is to use subquery to find the most recent data and then join with original table to get "dsqft0":
code :
select * from 
 ( select  p.scode,u.scode as uscode, current_date, dsqft0 
   from unit u
   join property p on p.hmy=u.hproperty
   join sqft sq on sq.hpointer=u.hmy
   where p.hmy=19 ) a
   join 
 (select p.scode,u.scode as uscode ,max(sq.dtdate)as currentdate
  from unit u
  join property p on p.hmy=u.hproperty
  join sqft sq on sq.hpointer=u.hmy
  where p.hmy=19 
  group by p.scode,u.scode ) sub 
  on a.scode = sub.scode
  and a.uscode = sub.uscode
  and a.current_date  = sub_current_date
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org