MS Access SQL查询——查找记录顺序

would like to ask how do we find out the 'numbering order' of a record.

我想问一下，我们如何找到记录的“编号顺序”。

My sql query -

我的sql查询-

SELECT *
FROM tableName
WHERE field2=444
ORDER BY field1

   field1 field2
1) 2/9/17 111
2) 3/9/17 222
3) 5/9/17 333
4) 8/9/17 444
5) 9/9/17 555

It would return 4 as 444 is on the Fourth record. Please advice.

它会返回4，因为444是第四个记录。请建议。

p/s - we hv a large DB so select * and compare each will be not efficient

p/s -我们hv有一个大的DB，所以选择*和比较每一个都不是有效的

Thank you,

谢谢你！

3 个解决方案

#1

You can use a subquery or DCount to count all rows lower than the previous one:

您可以使用子查询或DCount来计算所有低于前一个的行:

SELECT (SELECT Count(Field1) FROM tableName As B WHERE b.Field1 <= a.Field1) As numberingOrder, *
FROM tableName As A
WHERE field2=444
ORDER BY field1

And for only the distinct values, as asked in comments:

并且只针对不同的价值观，如评论所问:

SELECT (
     SELECT Count(Field1) 
     FROM (
         SELECT DISTINCT Field1 FROM tableName
     ) As B 
     WHERE b.Field1 <= a.Field1
) As numberingOrder, *
FROM tableName As A
WHERE field2=444
ORDER BY field1

#2

You can maybe do this:

你可以这样做:

SELECT COUNT(*) as Position_Of_Where_Clause
FROM YourTable t
WHERE t.field1 <= (SELECT TOP 1 s.field1
                   FROM YourTable s
                   WHERE s.field2 = 444
                   ORDER BY s.field1)

#3

If you want the fourth record and the values are all distinct, then you can use SELECT TOP twice:

如果你想要第四个记录，并且所有的值都是不同的，那么你可以使用SELECT TOP 2:

SELECT TOP 1 t.*
FROM (SELECT TOP 4 t.*
      FROM tableName as t
      WHERE field2 = 444
      ORDER BY field1
     ) as t
ORDER BY field1 DESC;

#1