Как в запросе найти номер строки

Как в запросе найти номер строки

Consider example table below

ProductDetailNo    ProductDescription
      224                Apples
      225                Tomatoes
      226                Potatoes

How do I list the row number for a selected row like below ?

RowNo    ProductDetailNo          Product Description
  2         225                Tomatoes

Using row_number() in my query just returns 1 always for a single record no mater what the logical row is in the database.

Thanks, Damien.

asked Jan 5, 2012 at 7:02

Zo Has's user avatar

5

try this

WITH MyTable AS
(
    SELECT ProductDetailNo, ProductDescription,
    ROW_NUMBER() OVER ( ORDER BY ProductDetailNo ) AS 'RowNumber'
    FROM Product
) 
SELECT RowNumber, ProductDetailNo     
FROM MyTable 
WHERE ProductDetailNo = 225

Hakan Fıstık's user avatar

Hakan Fıstık

16.4k13 gold badges107 silver badges130 bronze badges

answered Jan 5, 2012 at 7:07

Shoaib Shaikh's user avatar

Shoaib ShaikhShoaib Shaikh

4,5651 gold badge26 silver badges35 bronze badges

6

Please Check This 

WITH ArticleSearch AS
    (
        SELECT
            ROW_NUMBER() OVER 
                (
                    ORDER BY tblProducts.ProductDetailNo                                    
                ) AS RowNumber, 
            tblProducts.ProductDetailNo, 
            tblProducts.ProductDescription    
        FROM         
            tblProducts
    )

    SELECT 
        a.RowNumber AS SlNo,
        a.ProductDetailNo,
        a.ProductDescription
    FROM
          ArticleSearch a
    WHERE
          a.ProductDetailNo=225

answered Jan 5, 2012 at 7:22

Renju Vinod's user avatar

Renju VinodRenju Vinod

2541 gold badge3 silver badges11 bronze badges

1

What about this one?

SELECT RowNo, ProductDetailNo, ProductDescription
FROM (SELECT ROW_NUMBER() as RowNo, ProductDetailNo, ProductDescription
      FROM TheTable) as t
WHERE ProductDetailNo = 225;

answered Jan 5, 2012 at 7:09

Sergio Tulentsev's user avatar

Sergio TulentsevSergio Tulentsev

226k43 gold badges370 silver badges364 bronze badges

4

  WITH productCTE 
  AS
  (SELECT ROW_NUMBER() OVER(ORDER BY ProductDetailNo, ProductDescription) AS RowNo, ProductDetailNo, ProductDescription
   FROM tbl_Products
  )
  SELECT * FROM productCTE
  WHERE RowNo = 2

musefan's user avatar

musefan

47.7k21 gold badges134 silver badges184 bronze badges

answered Jan 5, 2012 at 9:04

Gopu's user avatar

GopuGopu

559 bronze badges

The row number you receive is from number of the rows of result. i.e. if your result has just one tuple, the row no. will always be 1.

To get row number of the entire table, you should add a extra attribute, a RowNo with auto increment to your table.

Hope this helps, but possibly SQL has even better solution for you!

answered Jan 5, 2012 at 7:07

Vinayak Garg's user avatar

Vinayak GargVinayak Garg

6,51810 gold badges53 silver badges80 bronze badges

1

There is no inherent row number for a table row. ROW_NUMBER() gives you the number of the row only within a specific result set. So it is the expected result that you always get 1 when the result set contains only 1 record. If you want a row number, your table schema should include something like an auto-incrementing IDENTITY column.

answered Jan 5, 2012 at 7:09

bobbymcr's user avatar

bobbymcrbobbymcr

23.7k3 gold badges55 silver badges66 bronze badges

1

Источник

В SQL есть специальная функция вывода номеров строк в запросе ROW_NUMBER(). Но в его младшем брате MySQL нет такой функции. Единственный способ вывести номер строки в MySQL – использование переменной.

Поставим задачу: необходимо вывести имя пользователя, количество его очков и место в рейтинге по количеству этих очков. Т.е. такую таблицу:

RANK POINTS NAME
1 500 Алексей
2 300 Сергей
3 200 Виталий

Получать данные будем из таблицы истории получения очков, которая зовётся “history”. Выглядит она так:

DATE POINTS NAME
26.03.2017 300 Алексей
16.08.2016 200 Алексей
11.01.2016 200 Виталий
28.12.2015 200 Сергей
14.07.2015 100 Сергей

Для начала получим суммы набранных очков, сгруппированные по именам пользователей и отсортированные по убыванию. Запрос будет выглядеть так:

SELECT SUM(POINTS) as ‘POINTS’, NAME FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC;

База вернёт следующую таблицу:

POINTS NAME
500 Алексей
300 Сергей
200 Виталий

Отлично! Теперь надо пронумеровать строки, чтобы получить место пользователя в рейтинге. Алексей будет первым, Сергей – вторым, Виталий – третьим.

Нумеровка делается через переменную, добавляемую к выводу. Казалось бы, можно сделать всё просто:

SET @rank=0;

SELECT @rank:=@rank+1 AS ‘RANK’, SELECT SUM(POINTS) as ‘POINTS’, NAME FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC;

Но если сделать такой запрос, то результат будет плачевным. Назначение рейтинга произойдёт после группировки, но до сортировки ORDER BY SUM(POINTS). Выглядеть результат будет так:

RANK POINTS NAME
1 500 Алексей
3 300 Сергей
2 200 Виталий

Цифры ранга идут не по порядку: 1, 3, 2. Чтобы исправить положение необходимо добавлять столбец ранга после группировки и сортировки, в отдельном запросе. По логике, получается SELECT в SELECT. Синтаксис будет такой:

SET @rank=0;
SELECT @rank:=@rank+1 AS ‘RANK’, POINTS, NAME FROM
(

SELECT SUM(POINTS) as ‘POINTS’, NAME
FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC

) as t ;

Обратите внимание на окончание запроса. Без “as t;” написать запрос нельзя, иначе будет ошибка “Every derived table must have its own alias!“.

Результат такого запроса будет выглядеть как нормальная ранговая таблица. С правильным расположением цифр ранга:

RANK POINTS NAME
1 500 Алексей
2 300 Сергей
3 200 Виталий

Теперь усложним задачу. Получим содержание одной ячейки такой таблицы.

Представим, что нам нужно вывести номер ранга в личный кабинет пользователя. Чтобы Алексей видел в своём личном кабинете цифру “1”, Сергей – “2”, а Виталий – “3”. Тогда запрос будет содержать SELECT внутри SELECT внутри SELECT. Вот так:

SET @rank=0;
SELECT RANK FROM
(

SELECT @rank:=@rank+1 AS ‘RANK’, NAME FROM
(

SELECT SUM(POINTS), NAME
FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC

) as t1

) as t2
WHERE NAME = ‘Виталий’;

Обратите внимание на хвосты: “as t1”, “as t2”. Нужны разные псевдонимы, иначе будет ошибка “Every derived table must have its own alias!“.

P.S.

Конечно же, на реальном проекте такая таблица будет содержать id пользователя, а не его имя. Потому что в строке правды нет, а в числе есть 🙂

Источник

Что делать, если нужно пронумеровать строки прямо в запросе? Полистав руководство MySql 5.5, нужной функции я не нашел.

Мне нужно было сохранять позиции пользователей в рейтинге, т.е. пронумеровать пользователей в зависимости от значения их рейтинга.

Используя PHP

Если совсем не думать, то можно загрузить рейтинг в PHP, получить порядковый номер строк, и сохранить эти позиции в базу.

(Структура данных в данном случае абстрактна) это будет выглядеть как серия запросов:

//получим список в порядке убывания рейтинга

SELECT user_id, rating_value FROM users ORDER BY rating_value DESC

//выполним серию запросов на сохранение полученных позиций

UPDATE users SET rating_position = %N WHERE user_id = %ID

Получится множество запросов под управлением программы на PHP. Если немного подумать, то тоже самое можно сделать без PHP и всего одним запросом.

Без использования PHP

Используем переменную для вычисления позиции при запросе данных

SET @n = 0;

SELECT (@n := @n + 1) AS POSITION, user_id, rating_value

FROM users ORDER BY rating_value DESC;

Для каждой строки MySql выполнит расчет позиции, так мы получим нужное значение POSITION (фактически, пронумеруем строки).

Встроим это сразу в UPDATE:

SET @n = 0;

UPDATE `users`,

    (SELECT (@n := @n + 1) AS POSITION, user_id

     FROM users ORDER BY rating_value DESC) as SRC

SET `users`.rating_position = SRC.POSITION

WHERE `users`.user_id = SRC.user_id;

Написать комментарий


Данная запись опубликована в 05.11.2016 16:10 и размещена в mySQL.
Вы можете перейти в конец страницы и оставить ваш комментарий.

Мало букафф? Читайте есчо !

Получить разницу дат в секундах в MySQL

Июнь 15, 2018 г.

Для начала выберем функцию с подходящей гранулярностью. DATEDIFF явно не подходит, т.к. даёт разницу в кол-ве целых дней. К счастью, есть аналогичная функция – TIMEDIFF, которая вычисляет разницу с точностью до микросекунд.

Результат работы TIMEDIFF …

Читать

Как узнать версию MySQL?

Сентябрь 3, 2018 г.

Получить версию бд можно как из консоли, так и средствами языка mySQL.

Версия из консоли UNIX
Тут все просто. Получаем версию из консоли.
[crayon-646c6038bb525692246528/]
Версия из SQL консоли
Как получить версию mySQL не из unix консоли, а средствами …

Читать

Источник

За последние 24 часа нас посетили 12816 программистов и 1103 робота. Сейчас ищет 441 программист …


  1. 234you

    234you
    Активный пользователь

    С нами с:
    31 июл 2012
    Сообщения:
    18
    Симпатии:
    0

    Вот в гугле искал-искал так и не могу найти.
    К примеру есть таблица users.
    В ней есть id, username
    Мне нужно найти какая по счету строка в таблице, допустим с `username` = ‘vasya’
    По id искать не вариант. в таблице может быть всего 100 строк и у всех id больше 200, к примеру.
    Нужна именно позиция строки в базе.
    Я из гугла понял, что копать нужно в numrow, но так и не разобрался, как оно работает.

    Всем спасибо!


  2. artoodetoo

    Команда форума
    Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    10.915
    Симпатии:
    1.197
    Адрес:
    там-сям

    В SQL базах нет такого понятия как номер строки в таблице! Это тебе не dbase какой-нибудь.

    В некоторых СУБД есть функции, выдающие порядковый номер строки в запросе — это не то же самое, согласись. Порядок сортировки поменяем через ORDER BY и номера будут соответствовать другим записям.

    Если тебе нужен номер строки в запросе — см. вот: http://stackoverflow.com/questions/3126972/mysql-row-number


  3. 234you

    234you
    Активный пользователь

    С нами с:
    31 июл 2012
    Сообщения:
    18
    Симпатии:
    0

    Не, то что ты дал по ссылке – это другое, хотя может подойти.
    Потому, что по ID в таком случаем можно порядок определить.
    То есть сортировать по ID, (ORDER BY `id` ASC).
    Но как это лучше сделать? Там по ссылке примеров несколько.
    Мне просто нужно выводить на страницу, какой по счету например VASYA в таблице.


  4. artoodetoo

    Команда форума
    Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    10.915
    Симпатии:
    1.197
    Адрес:
    там-сям

    Читай внимательно: номера строки таблицы не существует.

    “Выводить по счету” можно через фразу LIMIT <стартовый_номер>, <количество_строк>. Здесь имеются в виду номера по порядку в ЗАПРОСЕ, так же как и в примере по ссылке. Говорить про порядок и нумерацию можно только в терминах текущего запроса. А в исходных таблицах нет никаких номеров. Множества это неупорядоченный набор данных.


  5. 234you

    234you
    Активный пользователь

    С нами с:
    31 июл 2012
    Сообщения:
    18
    Симпатии:
    0

    Ну это понятно, что нет у MYSQL порядкового номера.
    Но как правильно организовать запрос, чтоб echo отобразило именно номер по счету того пользователя, который в очереди?
    Вот допустим есть таблица
    id user
    11 kolya
    12 den
    13 jenea

    Мне нужно, чтоб вывело, что Jenea на 3 месте.


  6. artoodetoo

    Команда форума
    Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    10.915
    Симпатии:
    1.197
    Адрес:
    там-сям

    Раз понятно, значит ты получил, что спрашивал. Пример по ссылке.


  7. 234you

    234you
    Активный пользователь

    С нами с:
    31 июл 2012
    Сообщения:
    18
    Симпатии:
    0

    6.         @curRow := @curRow + 1 AS row_number
    8. JOIN    (SELECT @curRow := 0) r
    10. echo $number[‘row_number’]; ?>

    мне такой код выводит букву “S”


  8. artoodetoo

    Команда форума
    Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    10.915
    Симпатии:
    1.197
    Адрес:
    там-сям

    всё ясно с тобой. быстро за учебники!


  9. 234you

    234you
    Активный пользователь

    С нами с:
    31 июл 2012
    Сообщения:
    18
    Симпатии:
    0

    Вот дельным советом помочь – никак… я кучу времени потрачу на учебники, пока найду то, что мне нужно.

    Добавлено спустя 7 минут 51 секунду:
    Твой совет беспонтовый короче оказался!
    $ushar_ex[‘id’] – ID текущего пользователя.

    1. $debilnii = mysql_num_rows(mysql_query(“SELECT * FROM `users` WHERE `id` <= ‘”.$ushar_ex[‘id’].”‘”));


  10. YSandro

    С нами с:
    7 апр 2011
    Сообщения:
    2.523
    Симпатии:
    2

    Можно сделать запрос, собрать все записи (их же там около 100?), отсортировать массив. Вот в массиве и будет всё по порядку. Да и ORDER BY ‘id’ по порядку выведет.
    Если нужно хранить какой-то свой порядок записей, то нужно в таблицу ввести новое поле с числами.Смысла нет в этой позиции. У записей есть уникальные ID – это самое главное. А порядок может быть любой, смотря как отсортируешь.


  11. sobachnik

Источник

В MySQL метод ROW NUMBER () содержит хронологический номер для каждой строки внутри раздела. Это просто своего рода оконная фишка. Число строк начинается с 1 с числа строк внутри раздела. Помните, что до версии 8.0 MySQL не разрешал функцию ROW NUMBER (), однако он предлагал переменную сеанса, которая помогает имитировать эту функцию. В этом руководстве мы узнаем больше о функциональности MySQL ROW NUMBER () и создадим последовательный номер для каждой строки в коллекции результатов. В MySQL методы ROW_NUMBER () используются либо с последующими предложениями:

  • В нем будет использоваться предложение Over ().
  • Предложение ORDERS BY упорядочивает результат в соответствии с порядком сортировки указанного столбца.

Содержание

  1. Синтаксис
  2. Пример 1: ROW_NUMBER () с использованием предложения ORDER BY
  3. Пример 2: ROW_NUMBER () с использованием предложения PARTITION BY
  4. Пример 3: ROW_NUMBER () с использованием PARTITION BY и ORDER BY
  5. Заключение

Синтаксис

>> SELECT col_name, ROW_NUMBER() OVER (PARTITION BY col_name, ORDER BY col_name) AS row_num FROM table_name;

Давайте откроем клиентскую оболочку командной строки MySQL из приложений и введем пароль для входа в систему.

Давайте откроем клиентскую оболочку командной строки MySQL

Вы должны создать новую таблицу или использовать таблицу по умолчанию, чтобы начать работу над функцией номера строки. Как показано на изображении ниже, у нас есть таблица «животные» в схеме «данные» с некоторыми записями в ней. Получим его записи с помощью инструкции SELECT.

>> SELECT * FROM data.animals;

Вы должны создать новую таблицу или использовать таблицу по умолчанию

Пример 1: ROW_NUMBER () с использованием предложения ORDER BY

Мы будем использовать ту же таблицу, чтобы подробно описать некоторые примеры функции номера строки. Мы берем пример функции ROW_NUMBER (), за которой следует Over (), при этом используем только предложение ORDER BY. Мы производили выборку всех записей при нумерации строк в соответствии с порядком столбца «Цена». Мы дали имя «row_num» столбцу, в котором будут храниться номера строк. Давайте попробуем следующую команду сделать это.

>> SELECT *, ROW_NUMBER() OVER ( ORDER BY Price ) AS row_num FROM data.animals;

После выполнения вышеуказанного запроса мы видим, что строкам были присвоены номера в соответствии с порядком сортировки столбца «Цена». Вы можете подумать, что некоторые меньшие цены должны быть вверху столбца, и он должен сортироваться в соответствии с этим. Но предложение ORDER BY видит только первую цифру или алфавит столбца для сортировки значений.

столбца для сортировки значений

Давайте выполним тот же запрос, за которым следует предложение ORDER BY, используя порядок сортировки столбца «Возраст». Результат будет указан в столбце «Возраст».

>> SELECT *, ROW_NUMBER() OVER ( ORDER BY Age ) AS row_num FROM data.animals;

Давайте выполним тот же запрос, за которым следует предложение

Пример 2: ROW_NUMBER () с использованием предложения PARTITION BY

Мы будем использовать единственное предложение PARTITION BY в запросе ROW_NUMBER () для проверки результатов. Мы использовали запрос SELECT для выборки записей, за которыми следовали ROW_NUMBER () и предложение OVER, при разделении таблицы в соответствии со столбцом «Цвет». Выполните добавленную ниже команду в командной оболочке.

>> SELECT *, ROW_NUMBER() OVER ( PARTITION BY Color ) AS row_num FROM data.animals;

В результате вы можете увидеть, что нумерация строк была назначена в разделах в соответствии с порядком сортировки цветов. Поскольку у нас есть 4 значения для цвета «Черный», который занимает 4 строки. Вот почему у него четырехрядные числа от 1 до 4 и наоборот.

В результате вы можете увидеть, что нумерация строк была

Попробуйте тот же пример, на этот раз разделенный столбцом «Пол». Как мы знаем, в этой таблице всего два пола, поэтому будет сформировано 2 раздела. Самки занимают 9 рядов, поэтому они имеют нумерацию от 1 до 9. У самцов 8 значений, поэтому от 1 до 8.

>> SELECT *, ROW_NUMBER() OVER ( PARTITION BY Gender ) AS row_num FROM data.animals;

Попробуйте тот же пример, на этот раз разделенный столбцом

Пример 3: ROW_NUMBER () с использованием PARTITION BY и ORDER BY

Мы выполнили два приведенных выше примера в командной строке MySQL, теперь пора выполнить пример ROW_NUMBER () в MySQL Workbench 8.0. Итак, откройте MySQL Workbench 8.0 из приложений. Подключите MySQL Workbench к корневой базе данных локального хоста, чтобы начать работу.

Индексы с использованием MySQL WorkBench

В левой части MySQL Workbench вы найдете панель схемы, взорвите навигатор. На этой панели схемы вы найдете список баз данных. В списке баз данных у вас будут разные таблицы и хранимые процедуры, как вы можете видеть на изображении ниже. В нашей базе данных есть разные таблицы. Мы откроем таблицу order1 с помощью команды SELECT в области запроса, чтобы начать использовать ее для реализации функции ROW_NUMBER ().

>> SELECT * FROM data.order1;

В левой части MySQL Workbench вы найдете панель схемы

Таблица «order1» была отображена в виде сетки, как показано ниже. Вы можете видеть, что у него есть 4 поля столбца: id, Region, Status и OrderNo. Мы будем извлекать все записи из этой таблицы, одновременно используя предложения ORDER BY и PARTITION BY.

была отображена в виде сетки, как показано ниже

В области запроса MySQL Workbench 8.0 введите показанный ниже запрос. Запрос был запущен с предложением SELECT, получая все записи, за которыми следует функция ROW_NUMBER () вместе с предложением OVER. После предложения OVER мы указали столбец «Статус», за которым следует оператор «PARTITION BY», чтобы разделить таблицу на разделы в соответствии с этой таблицей. Предложение ORDER BY используется для упорядочивания таблицы по убыванию в соответствии со столбцом «Регион». Номера строк будут сохранены в столбце «row_num». Нажмите на значок вспышки, чтобы выполнить эту команду.

В области запроса MySQL Workbench 8.0 введите показанный ниже запрос

Будет показан результат, показанный ниже. Прежде всего, таблица разделена на две части по значениям столбца «Статус». После этого он был представлен в порядке убывания столбца «Регион», и разделам были присвоены номера строк.

Будет показан результат, показанный ниже

Заключение

Наконец, мы завершили все необходимые примеры использования функции ROW_NUMBER () в MySQL Workbench и клиентской оболочке командной строки MySQL.

Источник

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