SQL Server Select Distinct Rows Using Most Recent Value Only

SQL server select distinct rows using most recent value only

One way

select t1.* from (select ForeignKeyId,AttributeName, max(Created) AS MaxCreated
from  YourTable
group by ForeignKeyId,AttributeName) t2
join YourTable t1 on t2.ForeignKeyId = t1.ForeignKeyId
and t2.AttributeName = t1.AttributeName
and t2.MaxCreated = t1.Created

See also Including an Aggregated Column's Related Values for 5 different ways to do this kind of query

SQL Select Distinct column and latest date

This is actually pretty easy to do using simple aggregation, like so:

select URL, max(DateVisited)
from <table>
group by URL

SQL query for select distinct with most recent timestamp first

try the following query with table1 as your tablename:

select username, location, max(timestamp) from table1
group by  username, location

Select Last Rows with Distinct Field

If I understand correctly, you would like to count the number of distinct item ids up to each each row (by date) and return all rows where the count is three.

If Postgres supported this, you could use:

select t.*
from (select t.*, 
             count(*) filter (where id = min_id) over (order by date desc) as cnt_itemid
      from (select t.*,
                   min(id) over (partition by itemid order by date desc) as min_id
            from t
           ) t
     ) t
where cnt_itemid <= 3;

Alas, Postgres does not support COUNT(DISTINCT) as a window function. But you can calculate it using DENSE_RANK():

select t.*
from (select t.*, 
             count(*) over (filter where id = min_id) as cnt_itemid
      from (select t.*,
                   min(id) over (partition by itemid order by date) as min_id
            from t
           ) t
     ) t
where cnt_itemid <= 3;

However, this returns all the most recent rows up before the 4th item -- so it has extra rows.

To get four rows, you want the first where the item id is "3". One method is:

select t.*
from (select t.*, min(id) filter (where cnt_itemid = 3) over () as min_cnt_itemid_3
      from (select t.*, 
                   count(*) filter (where id = min_id) over (order by date desc) as cnt_itemid
            from (select t.*,
                         min(id) over (partition by itemid order by date desc) as min_id
                  from t
                 ) t
           ) t
     ) t
where id <= min_cnt_itemid_3;

You can also do this by identifying the first occurrence of the "third item" and then choosing all rows up to that row:

select t.*
from t join
     (select itemid, min(max_date) over () as min_max_date
      from (select t.itemid, max(date) as max_date
            from t
            group by t.itemid
            order by max(t.date) desc
            limit 3
           ) t
      ) tt
      on t.itemid = tt.itemid and t.date >= tt.min_max_date;

This fiddle shows each of these.

SQL Server select distinct latest values

The max query didn't work because you included request_id in the grouping - try:

select distinct uname, role, max(request_id) as request_id 
from das_table where uname='jsmith'
group by uname, role

SQLFiddle here.

Get last distinct set of records

This should work for you.

 SELECT * 
 FROM [tableName] 
 WHERE id IN (SELECT MAX(id) FROM [tableName] GROUP BY code)

If id is AUTO_INCREMENT, there's no need to worry about the datetime which is far more expensive to compute, as the most recent datetime will also have the highest id.

Update: From a performance standpoint, make sure the id and code columns are indexed when dealing with a large number of records. If id is the primary key, this is built in, but you may need to add a non-clustered index covering code and id.

SQL select top five most recent row and distinct by a specific column

using row_number() to get the latest row for each EnvName, and only taking the top 5 from ordered Id desc

select top 5 *
from (
  select *
    , rn = row_number() over (partition by EnvName order by id desc)
  from appModelFlat 
  ) s
where rn = 1
order by id desc

top with ties version:

select top 5 *
from (
  select top 1 with ties *
  from appModelFlat
  order by row_number() over (partition by EnvName order by id desc)
  ) s
order by id desc

Select newest records that have distinct Name column

Select ID,Name, Price,Date
From  temp t1
where date = (select max(date) from temp where t1.name =temp.name)
order by date desc

Here is a SQL Fiddle with a demo of the above

---

Or as Conrad points out you can use an INNER JOIN (another SQL Fiddle with a demo) :

SELECT t1.ID, t1.Name, t1.Price, t1.Date 
FROM   temp t1 
INNER JOIN 
(
    SELECT Max(date) date, name
    FROM   temp 
    GROUP BY name 
) AS t2 
    ON t1.name = t2.name
    AND t1.date = t2.date 
ORDER BY date DESC 

---

Related Topics