Langsung ke konten utama

How To Get row_number in MySQL

 https://ubiq.co/database-blog/how-to-get-row_number-in-mysql/

SELECT t.*, @rownum := @rownum + 1 AS rank 
          FROM sales t, (SELECT @rownum := 0) r  
          order by amount desc;
 +------+---------------------+--------+------+
 | id   | order_date          | amount | rank |
 +------+---------------------+--------+------+
 |    1 | 2021-02-02 08:15:00 |    250 |    1 |
 |   10 | 2021-02-02 11:15:00 |    250 |    2 |
 |    5 | 2021-02-02 09:30:00 |    250 |    3 |
 |    9 | 2021-02-02 10:45:00 |    200 |    4 |
 |   12 | 2021-02-02 11:45:00 |    200 |    5 |
 |    6 | 2021-02-02 09:45:00 |    200 |    6 |
 |    2 | 2021-02-02 08:30:00 |    200 |    7 |
 |    7 | 2021-02-02 10:15:00 |    180 |    8 |
 |    3 | 2021-02-02 08:55:00 |    150 |    9 |
 |   11 | 2021-02-02 11:30:00 |    150 |   10 |
 |    4 | 2021-02-02 09:15:00 |    125 |   11 |
 |    8 | 2021-02-02 10:30:00 |    125 |   12 |
 +------+---------------------+--------+------+




Komentar

Postingan populer dari blog ini

REGEX: Regular Expressions

  Standards [ edit ] The  IEEE   POSIX  standard has three sets of compliance:  BRE  (Basic Regular Expressions), [29]   ERE  (Extended Regular Expressions), and  SRE  (Simple Regular Expressions). SRE is  deprecated , [30]  in favor of BRE, as both provide  backward compatibility . The subsection below covering the  character classes  applies to both BRE and ERE. BRE and ERE work together. ERE adds  ? ,  + , and  | , and it removes the need to escape the metacharacters  ( )  and  { } , which are  required  in BRE. Furthermore, as long as the POSIX standard syntax for regexes is adhered to, there can be, and often is, additional syntax to serve specific (yet POSIX compliant) applications. Although POSIX.2 leaves some implementation specifics undefined, BRE and ERE provide a "standard" which has since been adopted as the default syntax of many tools, where the choi...

Error when install new Instance or SSIS SQL Server 2008

Error : attributes do not match. present attributes (directory compressed) included attributes (0) Cause:  Sql server will validate the installation folder during the setup process to check if no Archive attribute as set on the installation folder The installation folder includes “C:\Program Files\Microsoft SQL Server and if you have it C:\Program Files (x86)\Microsoft SQL Server.”  Solution: - Go to SQL server installation directory - C:\Program Files\Microsoft SQL Server - C:\Program Files (x86)\Microsoft SQL Server - Right click on the folder and go to properties - Click on general tab and click on Advanced and uncheck the option “Folder is ready for archiving” - Try doing the installation again it will be successful

Difference: SQL UNION vs UNION ALL?

  Difference: SQL UNION vs UNION ALL ? UNION and UNION ALL are both used to retrieve records from multiple tables. This article will detail the differences between the two, allowing you to make the best choice for each unique scenario. You can use SQL’s  UNION  and  UNION ALL  commands to get data from multiple tables in your database. It’s a common use case, considering that most databases have many tables. Both  UNION  and  UNION ALL  are known as set operators. In SQL, set operators combine the results of two or more queries into a single result. You can  read more about set operators in this article . When comparing  UNION  vs.  UNION ALL , there is one major difference: UNION  only returns  unique UNION ALL  returns  all  records, including duplicates. If you feel like you would benefit from a well-structured, comprehensive course that covers foundational SQL, consider this  SQL...