Query Help

Question

Query Help

sql_butterfly

Ten Centuries

Points: 1264
More actions
January 12, 2011 at 12:05 am

#230027

Table Orders
TeamId OrderDate Value
--------------------------------
A1 2010-11-05 1200.00
A2 2010-11-05 1000.00
A1 2010-11-07 700.00
A1 2010-11-10 5000.00
A2 2010-11-09 1100.00
I need to write a wuery which can give the result:
TeamId OrderDate Value
--------------------------------
A1 2010-11-10 5000.00
A2 2010-11-09 1100.00
(I mean TeamId wise the latest order)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

I need to write a wuery which can give the result:
TeamId OrderDate Value
--------------------------------
A1 2010-11-10 5000.00
A2 2010-11-09 1100.00
(I mean TeamId wise the latest order)

SELECT DISTINCT TeamId, OrderDate, Value from yourtable

ORDER by OrderDate DESC

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 2

No No. I just need one record per Team Id. The one with latest Orderdate. Consider there are lots of Teams with hundrds of OrderDates.

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

sql_butterfly (1/12/2011)
No No. I just need one record per Team Id. The one with latest Orderdate. Consider there are lots of Teams with hundrds of OrderDates.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 4

sql_butterfly

Ten Centuries

Points: 1264

January 12, 2011 at 1:22 am

#1272217

Your message doesnot shows anything....

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Try this article

http://www.sqlservercentral.com/articles/T-SQL/71571/

Clear Sky SQL
My Blog[/url]

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 6

please post insert scripts in future.

try this

select a.TeamID, a.OD OrderDate, b.value

from

(select TeamID, max(OrderDate) OD

from yourtable

group by TeamID) a

join yourtable b

on a.TeamID=b.TeamID

and a.OrderDate=b.OD

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 7

sql_butterfly

Ten Centuries

Points: 1264

January 12, 2011 at 3:25 am

#1272256

Great. Thanks, that helped.

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 8

Pleasure,

If your table is rather large, I would insert the subquery in a temp table.