CODEWITHSUNDEEP
sql-serverjoin
ali
ali

Reputation: 11045

SQL JOIN with TOP ID from WHERE clause

I have the following tables:

-- t_products
prod_id ... name ... price
--------------------------
1           Prod1    23.2
2           Prod2    11.5
3           Prod3    10.0
4           Prod4    4.43 

-- t_products_lists
list_id ... prod_id ... date_created
1           1           2016-02-01
2           1           2015-31-12
3           3           2015-01-01
4           3           2015-12-01
5           4           2014-02-05
6           4           2012-24-06
7           2           2016-11-10

What I need is to get from t_products_lists all the rows that have date_created < 2016-01-01 (2,3,4,5,6).

Now, from those results, I only need the prod_id with the latest date_created, for all groups of duplicated prod_id. It will be required to get name and price from t_products and return along with date_created.

The result should be:

prod_id ... name ... price ... date_created
-------------------------------------------
1           Prod1    23.2      2015-31-12 (the only one before 2016-01-01)
3           Prod3    10.0      2015-12-01 (the latest date)
4           Prod4    4.43      2014-02-05 (the latest date)

I am trying to join the tables like this so far:

SELECT COUNT(prod.prod_id), prod.name, prod.price, prodlist.date_created 
FROM t_products_lists prodlist 
INNER JOIN t_products prod ON prodlist.prod_id = prod.prod_id 
WHERE prodlist.date_created < CONVERT(DATETIME,'01.01.2016 23:59:59.997',0) 
GROUP BY prod.prod_id, art.name, art.price, prodlist.date_created

but now I don't know how could I only get the prod_id with the latest date, because with this query it just returns all IDs before 01.01.2016

Upvotes: 2

Views: 45

Answers (2)

RussAwesome
RussAwesome

Reputation: 464

You will require something similar to the below. Instead of grouping by the date created, put a MAX around it in your select clause.

SELECT COUNT(prod.prod_id), prod.name, prod.price, MAX(prodlist.date_created )
FROM t_products_lists prodlist 
INNER JOIN t_products prod 
    ON prodlist.prod_id = prod.prod_id 
    WHERE prodlist.date_created < CONVERT(DATETIME,'01.01.2016 23:59:59.997',0) 
GROUP BY prod.prod_id, art.name, art.price

You can read a bit more about MAX at http://www.w3schools.com/sql/sql_func_max.asp

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

USE MAX( prodlist.date_created)

SELECT prod.prod_id, prod.name, prod.price, MAX( prodlist.date_created) 
FROM t_products_lists prodlist 
INNER JOIN t_products prod ON prodlist.prod_id = prod.prod_id 
WHERE prodlist.date_created < CONVERT(DATETIME,'01.01.2016 23:59:59.997',0) 
GROUP BY prod.prod_id, prod.name, prod.price

Upvotes: 1

Related Questions