467,923 Members | 1,250 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

Tricky group by order by query

Hello, I'm trying to find the most optimal way to perform a tricky
query. I'm hoping this is some sort of standard problem that has been
solved before, but I'm not finding anything too useful so far. I have
a solution that works (using subqueries), but is pretty slow.

Assume I have two tables:
[Item]
ItemID int (Primary Key)
ItemSourceID int
ItemUniversalKey uniqueidentifier
Price int

[Source]
ItemSourceID
Priority

I'm looking for a set of ItemIDs that match a query to the Price
(something like Price < 30), with a unique ItemUniversalKey, taking the
first item with each key according to Source.Priority.

So, given Item rows like this:
1 2 [key_one] 15
2 2 [key_two] 25
3 1 [key_one] 15

and Source rows like this:
1 1
2 2

I want results like this:
2 2 [key_two] 25
3 1 [key_one] 15

Row 1 in Item would be eliminated because it shares an ItemUniversalKey
with row 3, and row 3's Source.Priority is lower than row 1.

Help!?

Jul 23 '05 #1
  • viewed: 4350
Share:
5 Replies
Hi

Check out how to post DDL and example data at
http://www.aspfaq.com/etiquette.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.htm#inserts

CREATE TABLE [Item]
( ItemID int,
ItemSourceID int,
ItemUniversalKey CHAR(10),
Price int )

CREATE TABLE [Source]
( ItemSourceID Int,
Priority int )

INSERT INTO [Item]( ItemID, ItemSourceID, ItemUniversalKey, Price )
values ( 1, 2, '[key_one]', 15 )
INSERT INTO [Item]( ItemID, ItemSourceID, ItemUniversalKey, Price )
values ( 2, 2, '[key_two]', 25 )
INSERT INTO [Item]( ItemID, ItemSourceID, ItemUniversalKey, Price )
values ( 3, 1, '[key_one]', 15 )

INSERT INTO [Source]( ItemSourceID, Priority )
VALUES (1, 1)
INSERT INTO [Source]( ItemSourceID, Priority )
VALUES (2, 2)

SELECT i.ItemID, i.ItemSourceID, i.ItemUniversalKey, i.Price
FROM [Item] i
JOIN [Source] s ON i.ItemSourceID = s.ItemSourceID
WHERE i.Price = ( SELECT MAX(p.price) from [Item] p WHERE p.price < 30 AND
i.ItemSourceID = p.ItemSourceID )
ORDER BY s.priority desc

John

<sc***@spacehug.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Hello, I'm trying to find the most optimal way to perform a tricky
query. I'm hoping this is some sort of standard problem that has been
solved before, but I'm not finding anything too useful so far. I have
a solution that works (using subqueries), but is pretty slow.

Assume I have two tables:
[Item]
ItemID int (Primary Key)
ItemSourceID int
ItemUniversalKey uniqueidentifier
Price int

[Source]
ItemSourceID
Priority

I'm looking for a set of ItemIDs that match a query to the Price
(something like Price < 30), with a unique ItemUniversalKey, taking the
first item with each key according to Source.Priority.

So, given Item rows like this:
1 2 [key_one] 15
2 2 [key_two] 25
3 1 [key_one] 15

and Source rows like this:
1 1
2 2

I want results like this:
2 2 [key_two] 25
3 1 [key_one] 15

Row 1 in Item would be eliminated because it shares an ItemUniversalKey
with row 3, and row 3's Source.Priority is lower than row 1.

Help!?

Jul 23 '05 #2
Thanks for the etiquette tips, I'll keep those in mind moving forward.
Thanks as well for the query suggestion - however, it doesn't apply to
what I was asking. For example, if you add a couple more rows to the
table:

INSERT INTO [Source] (ItemSourceID, Priority)
VALUES (3, 3)

INSERT INTO [Item] (ItemID, ItemSourceID, ItemUniversalKey, Price)
VALUES (4, 3, '[key_one]', 25)

you'll end up with more than one row with [key_one] for the universal
key. Remember, the goal was that all of the universal keys be unique,
with the item coming from the source with the highest priority being
included. This is the working query that I have so far, but I'm
wondering if this is already a solved problem, with a more optimal
solution:

SELECT i.ItemID, i.ItemSourceID, i.ItemUniversalKey, i.Price
FROM [Item] i
INNER JOIN [Source] s ON i.ItemSourceID = s.ItemSourceID
INNER JOIN ( SELECT ItemUniversalKey, MIN(s.Priority) AS MinPriority
FROM [Item] i
INNER JOIN [Source] s ON i.ItemSourceID = s.ItemSourceID
GROUP BY i.ItemUniversalKey ) p
ON i.ItemUniversalKey = p.ItemUniversalKey AND s.Priority =
p.MinPriority
WHERE i.Price < 30

Thanks again ...

Jul 23 '05 #3
I am confused. Besides the missing DDL, your sample narrative shows a
Sources table without a key.

Can you explain what the logical differences are between the "item_id"
and the "item_universal_key"; you don't seem to know the ISO-11179 and
data modeling rules about having a description of the local usage of a
data element in its name. And of course you would never use any kind
of autonumbering in a base table.

Jul 23 '05 #4
This was just a quick stab at it, so you will want to go over it to see
if it fits the criteria and also if it performs better than what you
have. According to the execution plan, it has a cost that is about 13%
less than the solution that you have. This will really depend on your
indexes, etc. though.

Good luck,
-Tom.

SELECT I.ItemID, I.ItemSourceID, I.ItemUniversalKey, I.Price
FROM Item I
INNER JOIN Source S ON S.ItemSourceID = I.ItemSourceID
LEFT OUTER JOIN (Item I2 INNER JOIN Source S2 ON S2.ItemSourceID =
I2.ItemSourceID)
ON I2.ItemUniversalKey = I.ItemUniversalKey
AND I2.ItemID <> I.ItemID
AND S2.Priority < S.Priority
WHERE I.Price < 30
AND S2.ItemSourceID IS NULL

Jul 23 '05 #5
Hi

You can still move your derived table into the where clause and use
priority instead of price.

SELECT i.ItemID, i.ItemSourceID, i.ItemUniversalKey, i.Price
FROM [Item] i
JOIN [Source] s ON i.ItemSourceID = s.ItemSourceID
WHERE i.Price < 30
AND s.Priority = ( SELECT MIN(o.Priority) AS MinPriority
FROM [Item] t
INNER JOIN [Source] o ON t.ItemSourceID = o.ItemSourceID

where i.ItemUniversalKey = t.ItemUniversalKey )

As for most efficient that would require testing under live load
conditions and you would need to look at the query plan/indexes to make
sure the most suitable ones are in place.

John

sc***@spacehug.com wrote:
Thanks for the etiquette tips, I'll keep those in mind moving forward. Thanks as well for the query suggestion - however, it doesn't apply to what I was asking. For example, if you add a couple more rows to the
table:

INSERT INTO [Source] (ItemSourceID, Priority)
VALUES (3, 3)

INSERT INTO [Item] (ItemID, ItemSourceID, ItemUniversalKey, Price)
VALUES (4, 3, '[key_one]', 25)

you'll end up with more than one row with [key_one] for the universal
key. Remember, the goal was that all of the universal keys be unique, with the item coming from the source with the highest priority being
included. This is the working query that I have so far, but I'm
wondering if this is already a solved problem, with a more optimal
solution:

SELECT i.ItemID, i.ItemSourceID, i.ItemUniversalKey, i.Price
FROM [Item] i
INNER JOIN [Source] s ON i.ItemSourceID = s.ItemSourceID
INNER JOIN ( SELECT ItemUniversalKey, MIN(s.Priority) AS MinPriority
FROM [Item] i
INNER JOIN [Source] s ON i.ItemSourceID = s.ItemSourceID GROUP BY i.ItemUniversalKey ) p
ON i.ItemUniversalKey = p.ItemUniversalKey AND s.Priority =
p.MinPriority
WHERE i.Price < 30

Thanks again ...


Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Joel Thornton | last post: by
3 posts views Thread by Martin | last post: by
1 post views Thread by Pea | last post: by
15 posts views Thread by edouard.spooner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.