Sql как найти последнюю дату

I’m trying to get the last datetime record from a table that happens to store multiple status.
My table looks like so:

+---------+------------------------+-------+
|filename |Dates                   |Status |
+---------+------------------------+-------+
|abc.txt  |2012-02-14 12:04:45.397 |Open   |
|abc.txt  |2012-02-14 12:14:20.997 |Closed |
|abc.txt  |2013-02-14 12:20:59.407 |Open   |
|dfg.txt  |2012-02-14 12:14:20.997 |Closed |
|dfg.txt  |2013-02-14 12:20:59.407 |Open   |
+---------+------------------------+-------+

The results should be

+---------+------------------------+-------+
|filename |Dates                   |Status |
+---------+------------------------+-------+
|abc.txt  |2013-02-14 12:20:59.407 |Open   |
|dfg.txt  |2013-02-14 12:20:59.407 |Open   |
+---------+------------------------+-------+

Vadzim

24.6k11 gold badges138 silver badges151 bronze badges

asked May 14, 2013 at 18:55

If you want one row for each filename, reflecting a specific states and listing the most recent date then this is your friend:

select filename ,
       status   ,
       max_date = max( dates )
from some_table t
group by filename , status
having status = '<your-desired-status-here>'

Easy!

answered May 14, 2013 at 20:07

Nicholas CareyNicholas Carey

70.5k16 gold badges93 silver badges135 bronze badges

SELECT * FROM table
WHERE Dates IN (SELECT max(Dates) FROM table);

answered May 14, 2013 at 18:59

viditvidit

6,2633 gold badges32 silver badges50 bronze badges

SELECT TOP 1 * FROM foo ORDER BY Dates DESC

Will return one result with the latest date.

SELECT * FROM foo WHERE foo.Dates = (SELECT MAX(Dates) FROM foo)

Will return all results that have the same maximum date, to the milissecond.

This is for SQL Server. I’ll leave it up to you to use the DATEPART function if you want to use dates but not times.

answered May 14, 2013 at 19:01

Geeky GuyGeeky Guy

9,1894 gold badges41 silver badges62 bronze badges

this working

SELECT distinct filename
,last_value(dates)over (PARTITION BY filename ORDER BY filename)posd
,last_value(status)over (PARTITION BY filename ORDER BY filename )poss
FROM distemp.dbo.Shmy_table

answered Mar 14, 2016 at 11:37

shikosshikos

711 silver badge1 bronze badge

Considering that max(dates) can be different for each filename, my solution :

select filename, dates, status
from yt a
where a.dates = (
  select max(dates)
    from yt b
    where a.filename = b.filename
)
;

http://sqlfiddle.com/#!18/fdf8d/1/0

HTH

answered May 14, 2013 at 19:23

OsyOsy

1,6135 gold badges21 silver badges35 bronze badges

Exact syntax will of course depend upon database, but something like:

SELECT * FROM my_table WHERE (filename, Dates) IN (SELECT filename, Max(Dates) FROM my_table GROUP BY filename)

This will give you results exactly what you are asking for and displaying above. Fiddle: http://www.sqlfiddle.com/#!2/3af8a/1/0

answered May 14, 2013 at 19:00

Duffmaster33Duffmaster33

1,1609 silver badges16 bronze badges

select max(dates)
from yourTable
group by dates
having count(status) > 1

answered May 14, 2013 at 18:59

wootscootinboogiewootscootinboogie

8,39133 gold badges110 silver badges194 bronze badges

Try this:

SELECT filename,Dates,Status 
FROM TableName 
WHERE Dates In (SELECT MAX(Dates) FROM TableName GROUP BY filename)

duffy356

3,6783 gold badges32 silver badges47 bronze badges

answered Jul 27, 2015 at 13:45

warawara

313 bronze badges

Источник

I have a table that is a collection entries as to when a user was logged on.

username, date,      value
--------------------------
brad,     1/2/2010,  1.1
fred,     1/3/2010,  1.0
bob,      8/4/2009,  1.5
brad,     2/2/2010,  1.2
fred,     12/2/2009, 1.3

etc..

How do I create a query that would give me the latest date for each user?

Update: I forgot that I needed to have a value that goes along with the latest date.

asked Mar 9, 2010 at 18:35

This is the simple old school approach that works with almost any db engine, but you have to watch out for duplicates:

select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate

Using window functions will avoid any possible issues with duplicate records due to duplicate date values, so if your db engine allows it you can do something like this:

select x.username, x.date, x.value 
from (
    select username, date, value,
        row_number() over (partition by username order by date desc) as _rn
    from MyTable 
) x
where x._rn = 1

answered Mar 9, 2010 at 18:56

D’Arcy RittichD’Arcy Rittich

166k40 gold badges287 silver badges283 bronze badges

Using window functions (works in Oracle, Postgres 8.4, SQL Server 2005, DB2, Sybase, Firebird 3.0, MariaDB 10.3)

select * from (
    select
        username,
        date,
        value,
        row_number() over(partition by username order by date desc) as rn
    from
        yourtable
) t
where t.rn = 1

answered Mar 9, 2010 at 19:05

dotjoedotjoe

26.1k5 gold badges63 silver badges77 bronze badges

I see most of the developers use an inline query without considering its impact on huge data.

Simply, you can achieve this by:

SELECT a.username, a.date, a.value
FROM myTable a
LEFT OUTER JOIN myTable b
ON a.username = b.username 
AND a.date < b.date
WHERE b.username IS NULL
ORDER BY a.date desc;

ivan_pozdeev

33.5k17 gold badges105 silver badges150 bronze badges

answered Aug 18, 2016 at 11:20

sujeetsujeet

5775 silver badges8 bronze badges

From my experience the fastest way is to take each row for which there is no newer row in the table.

Another advantage is that the syntax used is very simple, and that the meaning of the query is rather easy to grasp (take all rows such that no newer row exists for the username being considered).

NOT EXISTS

SELECT username, value
FROM t
WHERE NOT EXISTS (
  SELECT *
  FROM t AS witness
  WHERE witness.username = t.username AND witness.date > t.date
);

ROW_NUMBER

SELECT username, value
FROM (
  SELECT username, value, row_number() OVER (PARTITION BY username ORDER BY date DESC) AS rn
  FROM t
) t2
WHERE rn = 1

INNER JOIN

SELECT t.username, t.value
FROM t
INNER JOIN (
  SELECT username, MAX(date) AS date
  FROM t
  GROUP BY username
) tm ON t.username = tm.username AND t.date = tm.date;

LEFT OUTER JOIN

SELECT username, value
FROM t
LEFT OUTER JOIN t AS w ON t.username = w.username AND t.date < w.date
WHERE w.username IS NULL

answered Aug 22, 2018 at 12:28

To get the whole row containing the max date for the user:

select username, date, value
from tablename where (username, date) in (
    select username, max(date) as date
    from tablename
    group by username
)

answered Mar 9, 2010 at 18:54

Alison R.Alison R.

4,17428 silver badges33 bronze badges

SELECT *     
FROM MyTable T1    
WHERE date = (
   SELECT max(date)
   FROM MyTable T2
   WHERE T1.username=T2.username
)

Hugo Dozois

8,07712 gold badges53 silver badges58 bronze badges

answered Jun 3, 2013 at 14:00

ManixManix

1211 silver badge2 bronze badges

This one should give you the correct result for your edited question.

The sub-query makes sure to find only rows of the latest date, and the outer GROUP BY will take care of ties. When there are two entries for the same date for the same user, it will return the one with the highest value.

SELECT t.username, t.date, MAX( t.value ) value
FROM your_table t
JOIN (
       SELECT username, MAX( date ) date
       FROM your_table
       GROUP BY username
) x ON ( x.username = t.username AND x.date = t.date )
GROUP BY t.username, t.date

answered Mar 9, 2010 at 18:54

Peter LangPeter Lang

54k27 gold badges148 silver badges161 bronze badges

If your database syntax supports it, then TOP 1 WITH TIES can be a lifesafer in combination with ROWNUMER.

With the example data you provided, use this query:

SELECT TOP 1 WITH TIES
  username, date, value
FROM user_log_in_attempts
ORDER BY ROW_NUMBER() OVER (PARTITION BY username ORDER BY date DESC)

It yields:

username | date      | value
-----------------------------
bob      | 8/4/2009  | 1.5
brad     | 2/2/2010  | 1.2
fred     | 12/2/2009 | 1.3

Demo

How it works:

ROWNUMBER() OVER (PARTITION BY... ORDER BY...) For each username a list of rows is calculated from the youngest (rownumber=1) to the oldest (rownumber=high)
ORDER BY ROWNUMBER... sorts the youngest rows of each user to the top, followed by the second-youngest rows of each user, and so on
TOP 1 WITH TIES Because each user has a youngest row, those youngest rows are equal in the sense of the sorting criteria (all have rownumber=1). All those youngest rows will be returned.

Tested with SQL-Server.

answered Oct 26, 2021 at 17:00

slartidanslartidan

20.1k15 gold badges81 silver badges129 bronze badges

SELECT DISTINCT Username, Dates,value 
FROM TableName
WHERE  Dates IN (SELECT  MAX(Dates) FROM TableName GROUP BY Username)


Username    Dates       value
bob         2010-02-02  1.2       
brad        2010-01-02  1.1       
fred        2010-01-03  1.0

answered Jul 27, 2015 at 13:41

warawara

313 bronze badges

This is similar to one of the answers above, but in my opinion it is a lot simpler and tidier. Also, shows a good use for the cross apply statement. For SQL Server 2005 and above…

select
    a.username,
    a.date,
    a.value,
from yourtable a
cross apply (select max(date) 'maxdate' from yourtable a1 where a.username=a1.username) b
where a.date=b.maxdate

answered Aug 20, 2018 at 6:36

You could also use analytical Rank Function

    with temp as 
(
select username, date, RANK() over (partition by username order by date desc) as rnk from t
)
select username, rnk from t where rnk = 1

answered Sep 26, 2018 at 1:42

imba22imba22

6511 gold badge12 silver badges25 bronze badges

SELECT MAX(DATE) AS dates 
FROM assignment  
JOIN paper_submission_detail ON  assignment.PAPER_SUB_ID = 
     paper_submission_detail.PAPER_SUB_ID

answered May 20, 2020 at 13:11

SELECT Username, date, value
 from MyTable mt
 inner join (select username, max(date) date
              from MyTable
              group by username) sub
  on sub.username = mt.username
   and sub.date = mt.date

Would address the updated problem. It might not work so well on large tables, even with good indexing.

answered Mar 9, 2010 at 18:53

Philip KelleyPhilip Kelley

39.2k11 gold badges57 silver badges92 bronze badges

SELECT *
FROM ReportStatus c
inner join ( SELECT 
  MAX(Date) AS MaxDate
  FROM ReportStatus ) m
on  c.date = m.maxdate

kleopatra

50.9k28 gold badges99 silver badges209 bronze badges

answered Dec 18, 2012 at 10:54

For Oracle sorts the result set in descending order and takes the first record, so you will get the latest record:

select * from mytable
where rownum = 1
order by date desc

skuntsel

11.6k11 gold badges44 silver badges67 bronze badges

answered Mar 5, 2015 at 19:26

SELECT t1.username, t1.date, value
FROM MyTable as t1
INNER JOIN (SELECT username, MAX(date)
            FROM MyTable
            GROUP BY username) as t2 ON  t2.username = t1.username AND t2.date = t1.date

Mike G

4,2129 gold badges40 silver badges65 bronze badges

answered Dec 2, 2014 at 21:06

DavidDavid

512 bronze badges

Select * from table1 where lastest_date=(select Max(latest_date) from table1 where user=yourUserName)

Inner Query will return the latest date for the current user, Outer query will pull all the data according to the inner query result.

answered Jan 4, 2017 at 10:12

I used this way to take the last record for each user that I have on my table.
It was a query to get last location for salesman as per recent time detected on PDA devices.

CREATE FUNCTION dbo.UsersLocation()
RETURNS TABLE
AS
RETURN
Select GS.UserID, MAX(GS.UTCDateTime) 'LastDate'
From USERGPS GS
where year(GS.UTCDateTime) = YEAR(GETDATE()) 
Group By GS.UserID
GO
select  gs.UserID, sl.LastDate, gs.Latitude , gs.Longitude
        from USERGPS gs
        inner join USER s on gs.SalesManNo = s.SalesmanNo 
        inner join dbo.UsersLocation() sl on gs.UserID= sl.UserID and gs.UTCDateTime = sl.LastDate 
        order by LastDate desc

Bugs

4,4919 gold badges31 silver badges41 bronze badges

answered Feb 27, 2017 at 13:18

My small compilation

self join better than nested select
but group by doesn’t give you primary key which is preferable for join
this key can be given by partition by in conjunction with first_value (docs)

So, here is a query:

select
 t.*
from 
 Table t inner join (
  select distinct first_value(ID) over(partition by GroupColumn order by DateColumn desc) as ID
  from Table
  where FilterColumn = 'value'
 ) j on t.ID = j.ID

Pros:

Filter data with where statement using any column
select any columns from filtered rows

Cons:

Need MS SQL Server starting with 2012.

answered Aug 23, 2017 at 4:55

resnyanskiyresnyanskiy

1,6081 gold badge24 silver badges24 bronze badges

I did somewhat for my application as it:

Below is the query:

select distinct i.userId,i.statusCheck, l.userName from internetstatus 
as i inner join login as l on i.userID=l.userID 
where nowtime in((select max(nowtime) from InternetStatus group by userID));

Hardik Shah

4,0322 gold badges19 silver badges41 bronze badges

answered Aug 7, 2018 at 12:02

Here’s one way to return only the most recent record for each user in SQL Server:

WITH CTE AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
  FROM your_table
)
SELECT *
FROM CTE
WHERE rn = 1;

This uses a common table expression (CTE) to assign a unique rn (row number) to each record for each user, based on the user_id and sorted in descending order by date. The final query then selects only the records with rn equal to 1, which represents the most recent record for each user.

answered Feb 3 at 16:41

Eli NunezEli Nunez

5395 silver badges10 bronze badges

SELECT * FROM TABEL1 WHERE DATE= (SELECT MAX(CREATED_DATE) FROM TABEL1)

Floern

33.4k24 gold badges104 silver badges119 bronze badges

answered May 29, 2017 at 11:22

You would use aggregate function MAX and GROUP BY

SELECT username, MAX(date), value FROM tablename GROUP BY username, value

answered Mar 9, 2010 at 18:37

Matthew JonesMatthew Jones

25.5k17 gold badges102 silver badges155 bronze badges

Источник

I just received an email from one of my regular readers who is curious to know if there is any way to find out when a table is recently updated (or last date time updated). I was ready with my answer! I promptly suggested him that if a table contains UpdatedDate or ModifiedDate date column with default together with value GETDATE(), he should make use of it. On close observation, the table is not required to keep history when any row is inserted. However, the sole prerequisite is to be aware of when any table has been updated. That’s it!

If a user wants to find out when was the last table updated he can query dynamic management view (DMV) – sys.dm_db_index_usage_stats and easily figure out when was the table updated last. Let us comprehend this example by creating a table and updating it. We can use DMV to determine when it was updated last.

USE AdventureWorks
GO
CREATE TABLE Test
(ID INT,
COL VARCHAR(100))
GO
INSERT INTO Test
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
GO

Now we have created a table and populated it with data. Next, we will run the following query to find out when it was last updated.

SELECT OBJECT_NAME(OBJECT_ID) AS TableName,
 last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')
AND OBJECT_ID=OBJECT_ID('test')

Running query provides accurate details of when was the table last updated. If WHERE condition is entirely removed it will provide details of the entire database.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Источник

Можете ли вы помочь мне построить sql-запрос, который вытаскивает из базы данных записи, которые имеют самую последнюю дату.

Я пробовал, но пришел к мертвым

SELECT *
FROM Table t,Table t2
WHERE t.date>t2.date

Я думаю, что мне нужно использовать некоторые временные таблицы… для меня это немного скучно

10 фев. 2011, в 11:39

Источник

2 ответа

Получить строки, упорядоченные по дате, по убыванию:

SELECT * FROM table ORDER BY date DESC

Извлеките 5 строк с самыми последними датами:

SELECT * FROM table ORDER BY date DESC LIMIT 5

Dan Grossman
10 фев. 2011, в 10:45

Try:

select * from Table where date_column = (select max(date_column) from Table)

dogbane
10 фев. 2011, в 11:13

Ещё вопросы

0php переместить пользователя на страницу 404 после ввода неверного URL
1Как определить, является ли вертикальная полоса прокрутки Grid Control видимой или скрытой. Дев экспресс
1Начальная точка анимации прогресса шестиугольника D3 js
1Android Studio 3.4 Hello World ОШИБКА: подключен плагин ‘java’, но он не совместим с плагинами Android
0php @ Azure Веб-сайт: не могу написать в% TMP%
1Manifest Merger Ошибка в проекте
0jQuery при наведении курсора мыши
0Как предупредить значение после инициализации представления с помощью requirejs и kendo mobile
0Почему `typename` все еще необходимо, даже если тип шаблона объявлен как` class`?
0Параметры: где они объявлены?
0Исправлено Nav, содержимое сайта затем перемещается вверх – Как это предотвратить?
0SQL соответствует условиям на основе максимального значения
0Yii2 RBAC Ошибка DbManager Вызов функции-члена getRole () со значением NULL
1Невозможно получить данные из базы данных
1Включить / отключить все входы и флажки с любым флажком
0Сортировка списка с помощью jquery UI
0Работа со ссылочными переменными в C ++
0MySQL Query, возвращающий дублированные результаты
1Функция хеширования Java MD5, дающая неправильный хеш
1Преобразование поэтажных планов в иерархические графики
0Соединить две таблицы подзапроса
0поиск подстроки в конце строки в запросе sql
1P2P рукопожатие без центрального сервера
0Проблема с SQL-запросом (работа с 3 таблицами)
0Наложение jQuery .fadeIn () (после просмотра другой вкладки браузера)
1OWIN – Selfhosting WebApi без определенного порта
1Базовая марионетка – как визуализировать CompositeView-> CollectionView-> ItemView
0Аутентификация пользователя с использованием сессий с PHP
1Ошибка доступа к базе данных из приложения MVC 4
0для каждого счетчика цикл в C ++ не увеличивается с определенными числами
0Xampp – завершение работы MySql – не работает
1Android – убийство не освобождает память?
0JQuery Назначение разницы ширины для элементов <li>
0Зависимости Бауэра для Angular в Eclipse
1Ошибка «JAVA_HOME не установлена» при установке pig. Что с этим делать?
0Как выровнять мои изображения по вертикали с помощью текстов
1Как я могу пройти и сократить объект?
1Оператор переключателя в Javascript
1three.js-Uncaught ReferenceError: сцена не определена
0Добавить одноэлементную функцию в класс Pimpl
0Как написать запрос для сопоставления по всем таблицам?
1Конвертер байтов в логическое значение (флажок)
0Глобальная переменная Twig для каждого пакета, Symfony2
1Как можно безопасно выполнить операции, а затем асинхронно вставить более 250 000 слов из файла .txt, не вызывая переполнение стека?
0Как выбрать только отфильтрованные данные таблицы в angularjs?
1Python – ускорить итерацию панд
0C ++ Двойная перезапись: невозможно преобразовать тип ‘в’ тип & ‘
0Ошибка синтаксиса TSQL: отсутствует ‘if’
1чтение файла xml и переупорядочение элементов на основе схемы xsd
1Intellij компилирует и собирает все классы (похоже, не использует кеш)

Источник

Как получить последнюю запись по последней дате?

Есть таблица tb.SO к которой я присоединяю ещё две таблицы через left join:
В таблице tb.Price записи по полям ID Brewery и Product code повторяются, меняться только цена и ID периода (в этой таблице 25мил записей)
В таблице tb.Period даты проливки цен
В результате нужно сделать выборку товаров только по последней дате (Period ID или Date Period) с ценой (Product price)

Вопрос задан

более двух лет назад
1789 просмотров

Пригласить эксперта

Проверьте следующий подход:

SELECT * FROM (
  SELECT
	  IDBrewery,
	  ProductCode,
	  ProductPrice,
	  PeriodDate,
	  ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY PeriodDate DESC) RN
  FROM Price
  JOIN Period ON Period.PeriodID = Price.PeriodID
) LastPrice
WHERE RN = 1
;

MSSQL fiddle here

Моё решение:

SELECT 
	 q.ID Brewery
	,q.Product code
	,q.Product price
	,q.Date Period
FROM 
	(
		SELECT DISTINCT
				,s.ID Brewery
				,s.Product code
				,MAX (pr.Date Period) OVER (PARTITION BY q.ID Brewery, q.Product code) AS j
		FROM [SO] s
		LEFT JOIN [Price] pr
		ON 		s.ID Brewery = pr.ID Brewery
		AND		s.Product code = pr.Product code

	) q
LEFT JOIN [Price] pr2
ON 		pr2.ID Brewery = q.ID Brewery
AND		pr2.Product code = q.Product code
AND		pr2.Date Period = q.Date Period

Slava Rozhnev Спасибо за оперативность и помошь)

Показать ещё
Загружается…

23 мая 2023, в 19:03

750 руб./за проект

23 мая 2023, в 18:54

3000 руб./за проект

23 мая 2023, в 18:54

2500 руб./за проект

Минуточку внимания

Источник

NOT EXISTS

ROW_NUMBER

INNER JOIN

LEFT OUTER JOIN

Related Posts

2 ответа

Ещё вопросы

Как получить последнюю запись по последней дате?

Минуточку внимания

Добавить комментарий Отменить ответ

NOT EXISTS

ROW_NUMBER

INNER JOIN

LEFT OUTER JOIN

Related Posts

2 ответа

Ещё вопросы

Как получить последнюю запись по последней дате?

Минуточку внимания

Вам также может понравиться

Как найти видеографа на свадьбу

Как найти массу подвижной системы

Как найти скопированные файлы в ноутбуке

Добавить комментарий Отменить ответ