0

I have a problem when I get this "16Nov010001" of previous record "16Oct310001".

I'm trying to order with date and serialno but not working. How can I solve this problem?

My queries are: 

SELECT TOP (1) serialno 
FROM dbo.Table 
WHERE (serialno < '16Nov010001') 
ORDER BY tranferdate, serialno DESC

and 

SELECT TOP (1) serialno 
FROM dbo.Table 
WHERE serialno < '16Nov010001' 
ORDER BY serialno DESC

Table records:

serialno    |           date 
------------------------------------------
16Nov010001 |   2016-11-01 11:28:40.640
16Nov010002 |   2016-11-01 11:32:38.530
16Nov010003 |   2016-11-01 11:35:08.437
16Nov010004 |   2016-11-01 11:36:30.117 
16Nov010005 |   2016-11-01 11:37:01.760
16Nov010006 |   2016-11-01 11:39:45.393
16Oct300001 |   2016-10-30 19:40:29.963
16Oct310001 |   2016-10-31 18:42:45.833
Amy
  • 131
  • 1
  • 12

2 Answers2

0

You are trying to use where saying that a string is less than another string, that won't work, if you want to order to do that, probably the date is the same of the order you could use the date instead, as far as I saw in your data will work.

You have the 16Nov010001 that was created in 2016-11-01 11:28:40.640 and you ahve the 16Nov010002 that was created in 2016-11-01 11:32:38.530 4 minutes later.

check it out. Use '=' or LIKE to compare strings in SQL?

Community
  • 1
  • 1
Galeixo
  • 102
  • 9
0

The desired output you have posted will we returned by this query

SELECT serialno, date
FROM dbo.Table 
WHERE serialno <= '16Nov010001' 
ORDER BY serialno DESC

I have removed the TOP 1, so it will return as many records as they are <= '16Nov010001'

PS: Remember that the ordering of a date string is not the same as its date value. For exemple '16Dic010001' < '16Nov010001'. So you may prefer to compare and order by "date" instead of "serialno".

SELECT serialno, date
FROM dbo.Table 
WHERE date <= '2016-11-01' 
ORDER BY serialno DESC
Marc Guillot
  • 6,090
  • 1
  • 15
  • 42