Select Newest Records That Have Distinct Name Column

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 

Select the 3 most recent records where the values of one column are distinct

It doesn't return what you expect because grouping happens before ordering, as reflected by the position of the clauses in the SQL statement. You're unfortunately going to have to get fancier to get the rows you want. Try this:

SELECT *
FROM `table`
WHERE `id` = (
    SELECT `id`
    FROM `table` as `alt`
    WHERE `alt`.`otheridentifier` = `table`.`otheridentifier`
    ORDER BY `time` DESC
    LIMIT 1
)
ORDER BY `time` DESC
LIMIT 3

Mysql latest record for distinct column

create a subquery to get the recent total_due of the customer

SELECT cus_id, (select total_due from invoice where inv_id=max(a.inv_id)) as total_due FROM invoice a GROUP BY cus_id ORDER BY inv_id DESC

Demo here

LINQ Select newest records that have distinct ForeignKeyId column

var werkzeugeImUmlauf = from w in dbContext.cpmWerkzeug
                                join e in dbContext.cpmEinAusgang
                                on w.Id equals e.WerkzeugId
                                where e.Id == dbContext.cpmEinAusgang.Where(x => x.WerkzeugId == e.WerkzeugId).Max(x => x.Id) select w;

This is the final solution. As mentioned by Mittal in his answer, it is possible to write a sub-query in LINQ.

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.

LINQ for getting the 5 latest records for each distinct name

Since you are using EF Core, you are limited in what you can do with GroupBy. Instead, you can replace GroupBy with a sub-query that returns which records you want for each name:

var allRecords = from s in _context.Students
                 where (from s2 in _context.Students
                        where s2.name == s.name
                        orderby s2.endTime descending
                        select s2.id).Take(5).Contains(s.id)
                 orderby s.name, s.endTime descending
                 select s;

Or, if you prefer the fluent version:

var allRecords2 = _context.Students.Where(s => _context.Students.Where(s2 => s2.name == s.name)
                                                       .OrderByDescending(s2 => s2.endTime)
                                                       .Select(s2 => s2.id)
                                                       .Take(5)
                                                       .Contains(s.id))
                                    .OrderBy(s => s.name)
                                    .ThenByDescending(s => s.endTime);

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

Getting last records of every distinct value of a column

You are selecting key which in group by means column1 on which you are grouping the values. One way to get the values as you expected is to try something like below:

var query = Messages.GroupBy(item => item.Column1).Select(group =>   group.Last().Id)
        .OrderBy(item => item.Id)
        .Select(a => a.Id)
        .ToList();

Or to get complete rows:

var query = Messages.GroupBy(item => item.Column1)
        .Select(group => group.Last())
        .OrderBy(item => item.Id)
        .Select(a => a)
        .ToList();

how to get latest record or record with max corresponding date of all distinct values in a column in mysql?

You can use left join as

select 
t1.* from table_name t1
left join table_name t2
on t1.Id = t2.Id and t1.Date >t2.Date
where t2.Id is null

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

---

Related Topics