inner join duplicate removal

Question

Post reply

inner join duplicate removal

usulusul

Newbie

Points: 2
More actions
July 29, 2008 at 3:07 am

#188804

Hello all,
I have an order table which is updated over time. I want to select for each orderid the row that corresponds to the first entry in time.
An excerpt from the table is listed below.
idorderdtorderidcomment
15/13/2008 7:54:13 AM100NULL
25/13/2008 7:54:13 AM100NULL
35/13/2008 7:54:13 AM101NULL
45/13/2008 7:54:14 AM101comment1
select tin.*
from (
select orderid,min(orderdt) as minOrderdt
from test
group by (orderid)
) as x inner join test as tin on tin.orderid=x.orderid and tin.orderdt=x.minOrderdt
The result of the query is:
12008-05-13 07:54:13.000100NULL
22008-05-13 07:54:13.000100NULL
32008-05-13 07:54:13.000101NULL
This is almost what I want. I think that since the orderid 100 appeared twice at the exact same time it is listed twice. How can I extend my query so that every orderid appears only once?
p.s. I am fairly new to sql so any help is highly appreciated.

Viewing 4 posts - 1 through 4 (of 4 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Is this what you're expecting to see?

DROP TABLE #Test

CREATE TABLE #Test (Rowid INT, orderdt DATETIME, orderid INT, comment VARCHAR(60))

INSERT INTO #Test (Rowid, orderdt, orderid, comment)

SELECT 1, CAST('13/05/2008 07:54:13' AS DATETIME), 100, NULL UNION ALL

SELECT 2, CAST('13/05/2008 07:54:13' AS DATETIME), 100, NULL UNION ALL

SELECT 3, CAST('13/05/2008 07:54:13' AS DATETIME), 101, NULL UNION ALL

SELECT 4, CAST('13/05/2008 07:54:14' AS DATETIME), 101, 'comment1'

SELECT x.*

FROM #Test x

INNER JOIN (

SELECT MIN(a.Rowid) AS minRowID, a.orderid, a.orderdt

FROM #Test a

INNER JOIN (SELECT orderid, MIN(orderdt) AS minorderdt FROM #Test GROUP BY orderid

) b ON b.orderid = a.orderid AND b.minorderdt = a.orderdt

GROUP BY a.orderid, a.orderdt

) y ON y.minRowID = x.Rowid

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

usulusul Newbie Points: 2 More actions · Answer 2

Chris,

Thanks a lot! So a second join on the rowid solved it!

Joins, not so easy (at least not to a beginner). Next time I join on something unique.

Regards,

Usul

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You're welcome, Usul.

Shame that, like us, you're not using 2k5. The solution is more elegant and much faster...

http://www.sqlservercentral.com/Forums/Topic537762-338-3.aspx#bm538585

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden