72596

How to get next/previous record number?

How to get previous record number in SQL Server?

Data Table:

Name | SO No. ----------------- Adrian | SO-00001 Adrian | SO-00002 Bianca | SO-00003 Carrie | SO-00004 Adrian | SO-00005 Bianca | SO-00006 Adrian | SO-00007

How can I get the result as below:

Name | SO No. | Previous SO ------------------------------- Adrian | SO-00005 | SO-00002

Answer1:

This one will work the same in all major RDBMS (including SQL Server and MySql)

<strong>UPDATED</strong> based on your comments

SELECT name, so_no, ( SELECT MAX(so_no) FROM table1 WHERE so_no < t.so_no AND name = t.name ) prev_so_no FROM table1 t WHERE so_no = 'SO-00005'

<strong>SQL Server:</strong>

<strong>UPDATED</strong> based on your comments

SELECT name, MAX(so_no) so_no, CASE WHEN MAX(so_no) = MIN(so_no) THEN NULL ELSE MIN(so_no) END prev_so_no FROM ( SELECT TOP 2 t1.name, t1.so_no FROM table1 t1 JOIN table1 t2 ON t1.name = t2.name WHERE t2.so_no = 'SO-00005' AND t1.so_no <= t2.so_no ORDER BY so_no DESC ) q GROUP BY name

if you're using <strong>SQL Server 2012</strong> then you can also utilize analytic function LAG

SELECT name, so_no, prev_so_no FROM ( SELECT name, so_no, LAG(so_no, 1, NULL) OVER (ORDER BY so_no) prev_so_no, ROW_NUMBER() OVER (ORDER BY so_no DESC) rnum FROM table1 WHERE name = 'Adrian' AND so_no <= 'SO-00005' ) q WHERE rnum = 1

or

SELECT TOP 1 name, so_no, prev_so_no FROM ( SELECT name, so_no, LAG(so_no, 1, NULL) OVER (ORDER BY so_no) prev_so_no FROM table1 WHERE name = 'Adrian' AND so_no <= 'SO-00005' ) q ORDER BY so_no DESC

<strong>MySQL:</strong>

SELECT name,         MAX(so_no) so_no,         CASE WHEN MAX(so_no) = MIN(so_no)              THEN NULL             ELSE MIN(so_no)        END prev_so_no   FROM (   SELECT name, so_no     FROM table1    WHERE name = 'Adrian'      AND so_no <= 'SO-00005'    ORDER BY so_no DESC    LIMIT 2 ) q    GROUP BY name

or

SELECT name, SUBSTRING_INDEX(so_no, ',', 1) so_no, CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(so_no, ',', 2), ',', -1) = SUBSTRING_INDEX(so_no, ',', 1) THEN NULL ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(so_no, ',', 2), ',', -1) END prev_so_no FROM ( SELECT name, GROUP_CONCAT(so_no ORDER BY so_no DESC) so_no FROM table1 WHERE name = 'Adrian' AND so_no <= 'SO-00005' GROUP BY name ) q <hr>

Output for all queries:

|   NAME |    SO_NO | PREV_SO_NO |
|--------|----------|------------|
| Adrian | SO-00005 |   SO-00002 |
</pre>

Here is <strong>SQLFiddle</strong> demo (<strong>SQL Server 2008</strong>) UPDATED Here is <strong>SQLFiddle</strong> demo (<strong>SQL Server 2012</strong>) Here is <strong>SQLFiddle</strong> demo (<strong>MySQL</strong>)

Answer2:

Try with offset, this way was not so clean but perhaps can help you with

SELECT name as "Name", (SELECT so_no FROM table WHERE name LIKE '%Adrian%' ORDER BY so_no DESC LIMIT 1) as "SO No.", (SELECT so_no FROM table WHERE name LIKE '%Adrian%' ORDER BY so_no DESC OFFSET 1 LIMIT 1) as "Previous SO" FROM table WHERE name LIKE '%Adrian%' LIMIT 1;

Sorry for bad query, kind of newbie also :(

Recommend

  • Does jdbc support persian language
  • FluentMigrator Failed Migrations Don't Rollback?
  • Insert records into two table at once
  • Two columns in subquery in where clause
  • Rails 4.1 environment variables not reloading
  • Python: Split a String Field into 3 Separate Fields using Lambda
  • end daemon processes with multiprocessing module
  • Does the MySQL IN clause execute the subquery multiple times?
  • d3.js selection conditional rendering
  • Use default value of a column in stored procedures
  • how to get username into sql trigger when multiple users signed on from asp membership
  • Getting short path in python
  • joining two bezier curves
  • Basic many-to-many left join query
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Clarification on min distance on LocationManager.requestLocationUpdates method, min Distance paramet
  • Implicit joins and Where in Doctrine - how?
  • RxJava debounce by arbitrary value
  • Run multiple queries from 1 SQL file showing result in multiple tables
  • Sequential (transactional) API calls in angular 4 with state management
  • D3 get axis values on zoom event
  • How to revert to previous XCode version?
  • C: Incompatible pointer type initializing
  • Use of this Javascript
  • Jetty Server not starting: Unable to establish loopback connection
  • C++ Partial template specialization - design simplification
  • Django: Count of Group Elements
  • recyclerView does not call the onBindViewHolder when scroll in the view
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • Confusion with PayPal's monthly billing cycle
  • Python: how to group similar lists together in a list of lists?
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal