CODEWITHSUNDEEP
sql
tyekhan
tyekhan

Reputation: 1

Sql Get the last 6

I need a sql query that use the Records ID to get the last 6 times of the trips also the number of time.

So the records in the table are like the following,

RecordID  Nooftime  Day&Time
1001        1         12/11/2009 14:11
1001        2         13/11/2009 12:11
1001        3         14/11/2009 11:11
1001        4         16/11/2009 14:11
1001        5         17/11/2009 14:11
1001        6         20/11/2009 13:11
1001        7         25/11/2009 09:11

I Need a query that show only the last 6 vist's and in one line.

Upvotes: 0

Views: 957

Answers (5)

kriss
kriss

Reputation: 24177

SELECT RecordID, NoofTime, Day&Time FROM table ORDER BY Day&Time DESC LIMIT 6;

depending on your SQL engine you will probably have to put some quotes around Day&Time.

The above syntax works with Mysql and PostgreSQL, for varous syntax used for this kins of request you can have a look there select-limit

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332581

Oracle

SELECT x.* 
  FROM (SELECT t.* 
          FROM TABLE t 
      ORDER BY day&time DESC) x
 WHERE ROWNUM <= 6

SQL Server

  SELECT TOP 6 t.* 
    FROM TABLE t 
ORDER BY day&time DESC

MySQL/Postgres

  SELECT t.* 
    FROM TABLE t 
ORDER BY day&time DESC
   LIMIT 6

Upvotes: 2

David Brunelle
David Brunelle

Reputation: 6440

For the same line portion, you can use a cursor if you are using SQL server 2008 (might work with 2005 I am not sure).

Upvotes: 0

Aaron
Aaron

Reputation: 2013

How about

SELECT TOP(6) *
FROM [Trips]
ORDER BY [Day&Time] DESC

I'm not sure what you mean by "in one line," but this will fetch the 6 most recent records.

Upvotes: 0

cmsjr
cmsjr

Reputation: 59185

If you're dealing with SQL Server try

Select Top 6 RecordID, NoOfTime, Day&Time from (table) order by Day&Time DESC

Upvotes: 6

Related Questions