advanced sorting
-
I am currently trying to join a Products table to a table of Registrations for those products. Simple enough but theres more...
I need to order the results by the type of registration (an int value) and then by the date of the registration.
Still simple but having done this...
I only want to bring back rows for each product with the latest date for each type and not the other dates for this product and that type of registration. There are a few thousand products and millions of registrations.
Nigel Moore
====================== -
And it's still not too difficult... But you might run into performance problems.
Anyway, here's how you should proceed :
1. Construct a query that returns the product, type and the max time. From what I understand, you already have this. Just add a GROUP BY product, registration_type and select the product, registration_type, max(time) as last_time.
2. Join this query with the complete products and registrations table on product, registration_type and last_time...
Got it? I'm not even sure if I still understand
. Should look something like this.SELECT product.*, registrations.*
FROM PRODUCT p INNER JOIN REGISTRATIONS r on p.product_id = r.product_id
INNER JOIN ON
(
SELECT product.product_id, registrations.type, max(registration.date) last_time FROM PRODUCT p1 INNER JOIN REGISTRATIONS r1 on p1.product_id = r1.product_id GROUP BY p.product_id, r.type
) temp
ON temp.product_id = p.product_id and temp.product_id = r.product_id and temp.type = r.type and temp.last_time = r.date
order by r.type, r.date
-
Here is an example that might help. This example brings back the most recient sales record for each store for the sales table in the pubs database:
use pubs
select * from sales a
where ord_date =
(select top 1 ord_date from sales
where a.stor_id = stor_id
order by ord_date desc)
You should be able to modify you query slightly to use the top function like above to return only those rows that have the latest date for each type.
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Is there an issue with just replacing the join with the inner query that you call temp with one containing all the fields required into the select and also the group by, rather then performing the join back onto the same tables again as above????
Nigel Moore
====================== -
nmoore,
Yes, there is an issue with selecting all fields in the 'temp' query.
Because there is a 'group by' clause, you can only select either the fields that are also in the 'group by' or use an aggregate function on any other field.
Just try to do it, and SQL Server will give you an error, with a rather clear description.
Gregory's query should return the same result, just moving the temp table from the join to a subquery. Don't know about the difference in performance between the two though.
-
Thanks people,
I was able to return what I needed (Just the product ids + name for those that matched my criteria) using just the Select and group by but I see that this would not always be the case.
Nigel Moore
====================== -
Glad you found a solution for yourself just wanted to go a step further on one comment. Gregory Larsen's would look like this and may offer some bennifits in the area of no temp table generated but subquery and less joining occurring.
Also, it is a good practice that if you use keywords in your code for column names or table names wrap them in [], better to not use keywords.
SELECT *
FROM product p
INNER JOIN registrations or
ON p.product_id = or.product_id
WHERE
or.[date] = (select max(ir.[date]) last_date FROM registrations ir WHERE ir.product_id = or.product_id)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
. Should look something like this.Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply