How to query the latest order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to query the latest order

Author

Topic

Spanjis
Starting Member

2 Posts

Posted - 2011-10-18 : 09:37:14

Hi,

I want only the latest order (the newest of date or the last entry) to be included in the query, any ideas?

I've tried with
select top 1 o.Created from webshop.[Order] o order by o.Created desc
and
select MAX(o.Created) from webshop.[Order] o
without any luck.

select distinct ltrim(rtrim(lower(o.Email))) from webshop.[Order] o
inner join webshop.ordersku osku on osku.OrderId = o.OrderId
inner join webshop.sku s on s.skuId = osku.skuid
where s.ProductId in (21,145)
and ((select top 1 o.Created from webshop.[Order] o order by o.Created desc) > GETDATE()-90 and o.SubTotal between 0 and 400
or (select top 1 o.Created from webshop.[Order] o order by o.Created desc) > GETDATE()-180 and o.SubTotal > 401)

Thanks!

Raymond

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 09:47:39

[code]select Email
from
(
select row_number() over (partition by s.ProductId order by o.Created desc) as rn,ltrim(rtrim(lower(o.Email))) as Email from webshop.[Order] o
inner join webshop.ordersku osku on osku.OrderId = o.OrderId
inner join webshop.sku s on s.skuId = osku.skuid
where s.ProductId in (21,145)
and ((o.Created > GETDATE()-90 and o.SubTotal between 0 and 400)
or (o.Created > GETDATE()-180 and o.SubTotal > 401))
)t
where rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Spanjis
Starting Member

2 Posts

Posted - 2011-11-11 : 09:44:25

Thanks visakh16, this helped me alot :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 09:55:02

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources