Latest record

My table are

Customer: customerId ,name

Order: orderId, customerId, product,date

I want to display latest order from the customer

You can use ORDER BY.

Select O.OrderId, C.Name, O.Product, O.DateFROM Order OJOIN Customer CON C.CustomerId O.CustomerIdORDER BY O.DateDesc

|||

SELECT orderId, customerId, product,[date], [name]FROM(select a.orderId, a.customerId, a.product,a.[date], b.name, row_number()over(partitionby a.customeridorderby [date]DESC)as RowNum

from [Order] ainnerjoin [Customer] bon a.customerId=b.customerId) t

WHERE RowNum= 1

sql

Last TSQL Command Batch

Does anyone know where Enterprise Manager (MSSQL 2000) gets the "Last TSQL Command Batch:" information from that shows when you display the properties for a process listed under current activity/Process Info for a server.

This could be useful for a bespoke system monitoring utility in my system.Google is your friend

http://www.developersdex.com/gurus/code/488.asp

last n records simpleand easy

how do i display last n record on 6.5
if i have a primary key(number)select pkey
, foo
from yourtable xxx
where n > (
select count(*)
from yourtable
where foo > xxx.foo )
"foo" is the column that determines the sequence

without a sequence, "top" has no meaning|||You mean an identity Column?

SELECT TOP n * FROM yourTable Order by PKID DESC|||Brett, when did sql server add support for TOP

7, wasn't it?

;) ;)|||doooooooh

Details...details...

Yup no TOP|||No i mean a primary key which is a number

select top 300 * from tablename
order by columnname desc (columnname is primary key)

Primary key is smallint

when i do

select clientid, foo
from client x
where 300>( select count(*)
from client
where foo> x.foo)

It gives error foo column not there

here after 6.5 sql became easy|||sjumma, what are the columns in your table?

which one do you want to sort by?|||let say i want 1 column

select clientid
from client

clientid is smallint and contunious number (1,2,3,...)

how will i make just the last 300 records be displayed|||In MS-SQL 6.5, I'd use:SET ROWCOUNT 300

SELECT clientid
FROM dbo.client AS a
ORDER BY clientid DESC

SET ROWCOUNT 0Note that an index on client.clientid will help performance immensely.

-PatP|||<sigh />

oh yeah, rowcount :p

select clientid
from client x
where 300>( select count(*)
from client
where clientid> x.clientid)|||Originally posted by r937
oh yeah, rowcount :p Mondo way ugly, but it gets an answer while I'm still young enough to care! I'd hate to contemplate how long it would take to slog the count query through a half million clients, even though it is esthetically more pleasant than the ROWCOUNT solution!

-PatP

Last Cube Process Date/Time

Is there anyway to programmatically retrieve the last date/time that an SSAS cube was processed?

I would like to display this date/time in reports.

I could add a step to the job that runs the process to add a row to a table upon successful completion, but don't want to re-invent the wheel.

Thanks!
BobPIt is available via both DSO (for AS2K) and AMO (for AS2K5).
Just look in BOL and you will see it.

_-_-_ Dave|||

hi,

can you post an example to show the date cube was last updated in a text box on a report.

Thanks,

|||

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices;

namespace GetCubeProcessingTime

{

class Program

{

static void Main(string[] args)

{

string date = string.Empty;

string cubeName = string.Empty;

try

{

// Connect to the SSAS server

Server server = new Server();

server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adventure Works DW;Data Source=localhost\YUKON");

// Get the Adventure Works cube(s)

Database database = server.Databases.GetByName("Adventure Works DW");

foreach (Cube cube in database.Cubes)

{

date = cube.LastProcessed.ToString("yyyy-MM-dd HH:mm:ss");

cubeName = cube.Name;

Console.WriteLine(string.Format("Cube [{0}] was processed: {1}", cubeName, date));

}

}

catch (Exception exception)

{

// Uups

Console.WriteLine(exception.Message);

}

}

}

}

Last Cube Process Date/Time

Is there anyway to programmatically retrieve the last date/time that an SSAS cube was processed?

I would like to display this date/time in reports.

I could add a step to the job that runs the process to add a row to a table upon successful completion, but don't want to re-invent the wheel.

Thanks!
BobPIt is available via both DSO (for AS2K) and AMO (for AS2K5).
Just look in BOL and you will see it.

_-_-_ Dave|||

hi,

can you post an example to show the date cube was last updated in a text box on a report.

Thanks,

|||

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices;

namespace GetCubeProcessingTime

{

class Program

{

static void Main(string[] args)

{

string date = string.Empty;

string cubeName = string.Empty;

try

{

// Connect to the SSAS server

Server server = new Server();

server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adventure Works DW;Data Source=localhost\YUKON");

// Get the Adventure Works cube(s)

Database database = server.Databases.GetByName("Adventure Works DW");

foreach (Cube cube in database.Cubes)

{

date = cube.LastProcessed.ToString("yyyy-MM-dd HH:mm:ss");

cubeName = cube.Name;

Console.WriteLine(string.Format("Cube [{0}] was processed: {1}", cubeName, date));

}

}

catch (Exception exception)

{

// Uups

Console.WriteLine(exception.Message);

}

}

}

}