SELECT last 5 rows of a table

[cymerman]

How do I select the last 5 rows of a table ?
I have to use "order by" I guess.

select * from tablename
order by col1 asce
where last 5

THANKS

 

[gmmastros]

You can't easily select the last 5 records, but you could order the data in descending order and select the top 5 of those.

[tt]
Select Top 5 *
From Table
Order By Column DESC
[/tt]

You could also re-order the data by making this a subquery, like this...

[tt][blue]
Select *
From (
Select Top 5 *
From Table
Order By Column [!]DESC[/!]
) As A
Order By Column [!]ASC[/!]
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[r937]

george, ANSI SQL please, not TOP

select * 
  from tablename as T
 where (
       select count(*)
         from tablename
        where col1 > T.col1 ) <  5

r937.com | rudy.ca

 

[cymerman]

r937,
you query is bringing 35 rows. If I use another collumn name that the result is 167 rows.

gmmastros,
Your query is giving me a sintax error.

 

[r937]

if it's bringing back 35 rows, that means you have a lot of ties for 5th place!!

can we see some sample data please? show us a half dozen rows or so, so that we can test it ourselves

r937.com | rudy.ca

 

[cymerman]

sure can
BTW, THANKS A LOT

res_code sales_site recess_days

SR SR2 10
CL HL 3
CL SR2 3
CL SC2 3
SR SR 10
CL CL 3
LO LC 3
CLX CLX 3
LOX LOX 3
LO SR2 3
LO SC2 3
DE SR2 3
DEX DEX 3
CL ML 3
SR PR 10

 

[r937]

would you also be kind enough to interpret what "the last five rows" means in the context of the data you've posted?

r937.com | rudy.ca

 

[cymerman]

res_code (resort code) sales_site (resort where the inventory is located) recess_days (recision days)
So, for the last row would be SR selling PR the recision days are 10. Sorry for not saying this before

 

[r937]

um, i think i see the problem

there is no such thing as the "last" row in a table

rows in a table do not have any sequence, they are simply stored somewhere on disk

you might, for example, declare an additional column into which you place the current timestamp when the row is inserted into the table, and then you can get the "last" rows by using ORDER BY on the timestamp column

otherwise, there is no way to get what you want



r937.com | rudy.ca

 

[cymerman]

What if I just want 10 rows of that table ?
Any 10 rows. How do I do ?

 

[r937]

are you sure you want an ANSI SQL solution? because you aren't likely to get one

perhaps you might mention which database system you're using

some databases have special non-standard features that you can use, like TOP or LIMIT



r937.com | rudy.ca

 

[cymerman]

Informix. Sorry if I posted in the worng place. If that is the case, lpease let me know where can I post for informix questions and btw, THANKS for helping.

 

[Mercury2]

cymerman ,

Is there any Relative Record number on Informix ?
If so, you could play with this RRN to get eg the last 5 rows of a table, otherwise you could create a stored procedure to do so.

 

[r937]

there are three informix forums to choose from --

forum179 IBM: Informix Dynamic Server
forum404 Informix solutions
forum876 Informix 4GL

r937.com | rudy.ca

 

[cymerman]

Mercury2,
There is no RRN. It is an olf informix version and sore procedure is something that i have to learn yet.

 

[PHV]

With informix you may play with ROWID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886