INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Retrieving latest entry for each set of two or more repeating keys by shnaeem
Posted: 13 Dec 01

You could more easily understand the scenerio with a simple example. Consider a table containing orders given by different customer for different products
Orders (Order_ID, Product_id, Cust_id, Orderdate, Description)
Customer_id and product_id are set of repeating values. They could have number of occurance with different dates of course.
Now you have to retrieve the latest order given by each customer for each product. One way is to write this query

Select max(order_date), cust_id, product_id
from orders
group by cust_id, product_id


and then place a join with the same table to retrieve the Order_id (As it is not in aggregation function)

There is another way around.
Use this query instead

Select max(Order_id), max(order_date), cust_id, product_id
from orders
group by cust_id, product_id


Isn't this great
and to increase the speed of the query use indexing. It really fly that way. Experience......

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close