Sql вывести все максимальные значения

Заметки Лёвика

web программирование, администрирование и всякая всячина, которая может оказаться полезной

Выборка записей с максимальным значением в MySQL

Выбираем записи с максимальным значением определенного поля.
На всякий случай — требуется получить не сами максимальные значения, а записи, в которых определенное поле равно максимальному.

Если поля всего два — сработает простой запрос:
id|date
select id, max(date) from table1;

При добавлении еще одного поля задача выбора записей с максимальным значением усложняется:

чтобы получить все записи с максимальной датой:

1) для MySQL версии 4.1 и выше можно сделать одним запросом:
select * from table1 where date in (select max(date) from table1)

2)Если версия ниже, то это надо делать в два запроса:

select @mxdate:=max(date) from table1;
select * from table1 where date=@mxdate;

id|date|count (id, date primary key) — next level

нужно вычислить count для каждого id с последней датой (с максимальным значением даты)

решение в один запрос рабочее, но нерациональное — на склейку и сравнение строк уходит неоправданно много времени
SELECT * FROM cms_catalog_goods_is_arc
WHERE CONCAT( date, id )
IN (SELECT concat( max( date ) , id ) FROM cms_catalog_goods_is_arc GROUP BY id )

Гораздо быстрей операция поиска строк с максимальным значением производится с использованием временных таблиц:

CREATE Temporary TABLE table2 (
`id` int(11) NOT NULL, `date` datetime NOT NULL, `cnt` int(11) NOT NULL,
PRIMARY KEY (`id`,`date`)
) ;
insert into table2 (id,date) select id, max(date) from table1 group by id;
update table2 t2, table1 t1 set t2.cnt=t1.cnt where t2.id=t1.id and t2.date=t1.date;

теперь в table2 все нужные нам записи.

ps. Довольно часто выполнить два простых запроса получается выгоднее, чем один сложный. http://www.samag.ru/art/07.2007/07.2007_02.html

pps В многих других СУБД (например, MS SQL, Oracle, PostgreSQL …) такого рода выборки решаются гораздо проще (с точки зрения пользователя БД) — достаточно одного незамудренного запроса. Что называется “в одну строчку” ..

В Oracle такого рода задача легко решается одним запросом. Причем, методов выбора строк с максимальным значением поля (даты в нашем случае) несколько. Часть из них приведена ниже (спасибо гостю SQL.ru с ником “Добрый Э — Эх” — http://sql.ru/forum/actualthread.aspx?tid=580229#5985168 )

1)
select *
from (
select t.*, row_number()
over(partition by num
order by dt desc) as rn
from t
)
where rn = 1;
2)
select t1.*
from t1
left join t2
on t1.num = t2.num
and t1.dt

Метки: mysql

Опубликовано Суббота, Август 2, 2008 в 05:44 в следующих категориях: Без рубрики. Вы можете подписаться на комментарии к этому сообщению через RSS 2.0. Вы можете добавить комментарий, или trackback со своего сайта.

Автор будет признателен, если Вы поделитесь ссылкой на статью, которая Вам помогла:
BB-код (для вставки на форум)

html-код (для вставки в ЖЖ, WP, blogger и на страницы сайта)

ссылка (для отправки по почте)

Комментарии (2) к записи “Выборка записей с максимальным значением в MySQL”

Привет всем!
Вчера нашел через гугл по запросу “разработка электроники на заказ цены”
Рекомендую!
Пока!

Огромное спасибо, Вы мне очень помогли, я искал именно выборку по определенному полю…

Источник

SQL MAX () с HAVING

MAX () функция с наличием

В этой статье мы обсудили, как SQL HAVING CLAUSE можно использовать вместе с SQL MAX (), чтобы найти максимальное значение столбца для каждой группы. SQL HAVING CLAUSE зарезервирован для статистической функции.

Использование предложения WHERE вместе с SQL MAX () также описано на этой странице.

SQL IN OPERATOR, который проверяет значение в наборе значений и извлекает строки из таблицы, также может использоваться с функцией MAX.

Пример :

Образец таблицы: клиент

Чтобы получить данные ‘cust_city’, ‘cust_country’ и максимальный размер «привели_амт» из таблицы клиентов при следующих условиях —

1. комбинация cust_country и cust_city должна создать группу

можно использовать следующий оператор SQL:

Иллюстрированная презентация:

SQL MAX () где

Образец таблицы: клиент

Чтобы получить данные «cust_country» и максимального значения «yal_amt »из таблицы« customer »с соблюдением следующих условий:

1. ‘cust_country’ должен быть отформатирован в группе,

2. оценка должна быть 2,

можно использовать следующий оператор SQL:

Иллюстрированная презентация:

SQL MAX () с оператором IN

Образец таблицы: клиент

Чтобы получить данные для «открытие_amt» и максимум «выдающиеся_amt» из таблицы «клиент» при следующих условиях —

1. «Opening_amt» должен прийти в группу,

2. значение ‘creation_amt’ должно быть 3000, или 8000, или 10000,

можно использовать следующий оператор SQL:

Иллюстрированная презентация:

SQL MAX () с HAVING и IN

Пример таблицы: заказы

Чтобы получить данные «agent_code», количество агентов в виде «count (agent_code)» и максимальное значение ord_amount из таблицы «orders» при следующих условиях:

1. ‘agent_code’ должен быть отформатирован в группе,

2. максимальное значение ord_amount должно быть 500, 800 или 2000,

можно использовать следующий оператор SQL:

Примечание. Выводы указанного оператора SQL, показанного здесь, взяты с использованием Oracle Database 10g Express Edition.

Вот слайд-презентация всех агрегатных функций.

Источник

Агрегатные функции SQL — SUM, MIN, MAX, AVG, COUNT

Оглавление

• Функция SQL SUM
• Функция SQL MIN
• Функция SQL MAX
• Функция SQL AVG
• Функция SQL COUNT
• Агрегатные функции вместе с SQL GROUP BY (группировкой)
• Разрешены ли агрегатные функции от агрегатных функций?

Связанные темы

• Оператор SELECT
• GROUP BY — группировка в запросах

Назад >>

Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.

Функция SQL SUM

Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:

После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

Сначала работать будем с базой данных фирмы — Company1. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке .

Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Для получения суммы размеров всех заработных плат используем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Этот запрос вернёт значение 287664,63.

А теперь упражнение для самостоятельного решения. В упражнениях уже начинаем усложнять задания, приближая их к тем, что встречаются на практике.

Пример 2. Вывести сумму комиссионных, получаемых всеми сотрудниками с должностью Clerk.

Функция SQL MIN

Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.

Пример 3. База данных и таблица — те же, что и в примере 1.

Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Запрос вернёт значение 10505,90.

И вновь упражнение для самостоятельного решения. В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:

Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.

Функция SQL MAX

Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.

Пример 5. База данных и таблица — те же, что и в предыдущих примерах.

Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Запрос вернёт значение 18352,80

Пришло время упражнения для самостоятельного решения.

Пример 6. Вновь работаем с двумя таблицами — Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц).

Функция SQL AVG

Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG. Эта функция возвращает среднее значение среди всех значений столбца.

Пример 7. База данных и таблица — те же, что и в предыдущих примерах.

Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Результатом будет значение 6,33

В следующем упражнении для самостоятельного решения помимо агрегатной функции требуется использовать также предикат BETWEEN.

Пример 8. Работаем с одной таблицей — Staff. Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет.

Функция SQL COUNT

Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать SELECT COUNT(ИМЯ_СТОЛБЦА) . то результатом будет количество записей без учёта тех записей, в которых значением столбца является NULL (неопределённое). Если использовать в качестве аргумента звёздочку и начать запрос SELECT COUNT(*) . то результатом будет количество всех записей (строк) таблицы.

Пример 9. База данных и таблица — те же, что и в предыдущих примерах.

Требуется узнать число всех сотрудников, которые получают комиссионные. Число сотрудников, у которых значения столбца Comm — не NULL, вернёт следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Результатом будет значение 11.

Пример 10. База данных и таблица — те же, что и в предыдущих примерах.

Если требуется узнать общее количество записей в таблице, то применяем запрос со звёздочкой в качестве аргумента функции COUNT (на MS SQL Server — с предваряющей конструкцией USE company1;):

Результатом будет значение 17.

В следующем упражнении для самостоятельного решения потребуется использовать подзапрос.

Пример 11. Работаем с одной таблицей — Staff. Вывести число сотрудников в отделе планирования (Plains).

Агрегатные функции вместе с SQL GROUP BY (группировкой)

Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY служит для группировки результирующих значений по столбцам таблицы базы данных. На сайте есть урок, посвящённый отдельно этому оператору.

Работать будем с базой данных «Портал объявлений 1». Скрипт для создания этой базы данных, её таблицы и заполения таблицы данных — в файле по этой ссылке .

Пример 12. Итак, есть база данных портала объявлений. В ней есть таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит данные о больших категориях объявлений (например, Недвижимость), а столбец Parts — о более мелких частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость). Столбец Units содержит данные о количестве поданных объявлений, а столбец Money — о денежных суммах, вырученных за подачу объявлений.

Category	Part	Units	Money
Транспорт	Автомашины	110	17600
Недвижимость	Квартиры	89	18690
Недвижимость	Дачи	57	11970
Транспорт	Мотоциклы	131	20960
Стройматериалы	Доски	68	7140
Электротехника	Телевизоры	127	8255
Электротехника	Холодильники	137	8905
Стройматериалы	Регипс	112	11760
Досуг	Книги	96	6240
Недвижимость	Дома	47	9870
Досуг	Музыка	117	7605
Досуг	Игры	41	2665

Используя оператор SQL GROUP BY, найти суммы денег, вырученных за подачу объявлений в каждой категории. Пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):

Результатом будет следующая таблица:

Category	Money
Досуг	16510
Недвижимость	40530
Стройматериалы	18900
Транспорт	38560
Электротехника	17160

Пример 13. База данных и таблица — та же, что в предыдущем примере.

Используя оператор SQL GROUP BY, выяснить, в какой части каждой категории было подано наибольшее число объявлений. Пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):

Результатом будет следующая таблица:

Category	Part	Maximum
Досуг	Музыка	117
Недвижимость	Квартиры	89
Стройматериалы	Регипс	112
Транспорт	Мотоциклы	131
Электротехника	Холодильники	137

Итоговые и индивидуальные значения в одной таблице можно получить объединением результатов запросов с помощью оператора UNION.

Источник