Power By 专否社区

how to get last 4 records without auto-increment ID

Go To StackoverFlow.com

1

I have a table with an primarykey that is not auto-increment. Here PK is seeker_nic and I want to get the last 4 records of this table.. 

 SEEKER
    seeker_nic     Name
    ---------------------
    81025          ali
    81013          bilal
    87651          hamza 
    78911          biya
    98726          saqib
    62528          mirza

I want this

seeker_nic     Name
---------------------
87651          hamza 
78911          biya
98726          saqib
62528          mirza

I could do this if table had id (PK) with auto-increment... But i have no idea how to get record with this PK seeker_nic...

2012-04-05 16:40
by maham
You have a timestamp column as part of the table schema - Mike Purcell 2012-04-05 16:42
Are there any other columns in this table - NullUserException 2012-04-05 16:43
Can't you make an SQL query ordering by created_at and limiting the results to 4 - rapcal 2012-04-05 16:43
A lesson learned, seeker_nic should be a index key, add a auto incrementing id of sorts.. - Lawrence Cherone 2012-04-05 16:44

1

Auto-increment is not the issue. The issue is that rows in a relational database have no implied order, so there's no way to determine which are the "last 4 records" unless you have a column with that information.

From the information you've given us in the question there's no way for the database to order your records: neither seeker_nic nor name can be ordered to give you the records the way you want them.

If you had a column that could be ordered, like a creation_date or id column, you could do:

  SELECT seeker_nic, name 
    FROM seeker
ORDER BY <column_here> DESC
   LIMIT 4                   -- syntax may vary depending on RDMBS

Just because you have a natural key (seeker_nic), doesn't mean you can't have an auto-incrementing surrogate key.

If you don't have a column that can determine how the columns should be ordered, you are short of luck.

2012-04-05 16:48
by NullUserException
ok thnx for your suggesstion.... : - maham 2012-04-05 16:57
Ads