Please help with Query - 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

General SQL Server Forums

New to SQL Server Programming

Please help with Query

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-02-03 : 08:55:15

I wish I could solve this by myself; seems easy but I just dont get it:

GROUPID  POSITION   ITEMID
--------------------------
XXXX1       1       YYY1
XXXX1       2       YYY2
XXXX1       3       YYY1
XXXX1       4       YYY3

The Query should filter those records, where in a group defined by the same GROUPID, the same ITEMID occurs more than once. These records should NOT be just ommited, but I want the Query to return that record with the lowest POSITION (is unique) for that not-unique ITEMID. So for that example the query returns:

GROUPID  POSITION   ITEMID
--------------------------
XXXX1       1       YYY1
XXXX1       2       YYY2
XXXX1       4       YYY3

Martin

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-03 : 09:11:08

Isn't it just as easy as:


SELECT
    [GROUPID]
    , [ITEMID]
    , MIN([POSITION])
FROM
    <dataset>
GROUP BY
    [GROUPID]
    , [ITEMID]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-02-06 : 05:03:07

Back from the weekend... hope everybody had a nice weekend too!

Thank you Charlie! Yep, your query does the job in this case. But though there are more than the 3 columns GroupID, Position, ItemID it wont work anymore as soon as I add column4 to the select and consequently also to the group-by clause . I guess I need to build a self join linking POSITION to MIN(POSITION) to get these columns

Martin

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-02-06 : 06:59:42

Thanks to Charlie I found it:

select t0.GroupID, t0.ItemID, t0.Position, t0.Column4, t0.Column5... from table t0 inner join
(SELECT t1.GroupID, t1.ItemID, MIN(t1.POSITION) as POS
FROM table t1
GROUP BY t1.GroupID, t1.ItemID) t1
ON t1.GroupID=t0.GroupID and t1.ItemID=t0.ItemID and t1.POS=t0.POSITION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources