0

I want to retrieve the 2nd last row result and I have seen this question:

How can I retrieve second last row?

but it uses order by which in my case does not work because the Emp_Number Column contains number of rows and date time stamp that mixes data if I use order by .

Table View

The rows 22 and 23 contain the total number of rows (excluding row 21 and 22) and the time and day it got entered respectively.

I used this query which returns the required result 21 but if this number increases it will cause an error.

SELECT TOP 1 * 
FROM(
    SELECT TOP 2 * 
    FROM DAT_History
    ORDER BY Emp_Number ASC 
    ) t                     
ORDER BY Emp_Number desc

Is there any way to get the 2nd last row value without using the Order By function?

Community
  • 1
  • 1
Alexander
  • 313
  • 7
  • 20
  • There are a number of things wrong here. First, is this mysql or sql-server? Second is this "if this number increases it will cause an error." What does that mean? And last but not least is the apparently completely insane data here. Why is the "last" rows value a datetime? And why is the next row the count of rows "before" it? Why in the world do you have a table like this? This just seems like truly awful design. – Sean Lange Aug 22 '16 at 14:34
  • 1
    Relational databases do not have a notion of "order" so without using order by any element could be returned as the "2nd to last" because the RDBMS don't have to account for ordering. – apokryfos Aug 22 '16 at 14:37
  • @SeanLange I corrected it to mysql. 2nd I meant if the number of rows gets greater then employee number range then I will get employee number instead of the actual value. 3rd that's life I have no control how the table is designed – Alexander Aug 22 '16 at 14:40
  • You are fighting a losing battle here. Storing multiple types of data in a single column is fraught with problems. Especially in this case when the order of those means something. You can fix it for a while but at some point this horrible design is going to fight back and win. – Sean Lange Aug 22 '16 at 14:57
  • The `TOP` syntax is from Microsoft SQL Server, not MySQL. I have edited the tags for this question. – Bill Karwin Aug 22 '16 at 16:14

2 Answers2

1

There is no guarantee that the count will be returned in the one-but-last row, as there is no definite order defined. Even if those records were written in the correct order, the engine is free to return the records in any order, unless you specify an order by clause. But apparently you don't have a column to put in that clause to reproduce the intended order.

I propose these solutions:

1. Return the minimum of those values that represent positive integers

select min(Emp_Number * 1)
from   DAT_history 
where  Emp_Number not regexp '[^0-9]'

See SQL Fiddle

This will obviously fail when the count is larger then the smallest employee number. But seeing the sample data, that would represent a number of records that is maybe not expected...

2. Count the records, ignoring the 2 aggregated records

select count(*)-2
from   DAT_history

See SQL Fiddle

3. Relying on correct order without order by

As explained at the start, you cannot rely on the order, but if for some reason you still want to rely on this, you can use a variable to number the rows in a sub query, and then pick out the one that has been attributed the one-but-last number:

select Emp_Number * 1
from   (select Emp_Number,
               @rn := @rn + 1 rn
        from   DAT_history,
               (select @rn := 0) init        
       ) numbered
where  rn = @rn - 1

See SQL Fiddle

The * 1 is added to convert the text to a number data type.

trincot
  • 317,000
  • 35
  • 244
  • 286
0

This is not a perfect solution. I am making some assumptions for this. Check if this could work for you.

;WITH cte 
     AS (SELECT emp_number, 
                Row_number() 
                  OVER ( 
                    ORDER BY emp_number ASC) AS rn 
         FROM   dat_history 
         WHERE  Isdate(emp_number) = 0) --Omit date entries
SELECT emp_number 
FROM   cte 
WHERE  rn = 1 -- select the minimum entry, assuming it would be the count and assuming count might not exceed the emp number range of 9888000
Muthukumar
  • 8,679
  • 17
  • 61
  • 86