query problem - 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 2005 Forums

Transact-SQL (2005)

query problem

Author

Topic

Lambik
Starting Member

13 Posts

Posted - 2010-12-30 : 06:20:54

Hi guys I need some help

I have 2 table's.
tableA with product, startdate
tableB with product, startdate

I want to have a query which return for all the products in both the tables
the oldest startdate.

contents:
tableA
prod startdate
A 1 jun 2006
A 5 sep 2009
B 1 may 2007

tableA
prod startdate
A 1 oct 2010
B 1 okt 2001

the query must return

prod startdate
A 1 jun 2006
B 1 okt 2001

I can make the queries for the table's apart but I need
one queury which returns the result for both the table's

Lambik

matty
Posting Yak Master

161 Posts

Posted - 2010-12-30 : 06:27:55

[code]
SELECT prod, startdate
FROM
(
SELECT prod, startdate,ROW_NUMBER() over(PARTITION BY prod ORDER BY startDate) AS Rownum
FROM
(
SELECT prod, startdate
from tableA
UNION ALL
SELECT prod, startdate
from tableB
)t
) p
WHERE p.Rownum = 1
[/code]

Lambik
Starting Member

13 Posts

Posted - 2010-12-30 : 07:46:25

matty, many many thanx it's working great

matty
Posting Yak Master

161 Posts

Posted - 2010-12-30 : 23:37:17

welcome :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources