473,837 Members | 1,515 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
ItemUniversalKe y uniqueidentifie r
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 ItemUniversalKe y, 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 ItemUniversalKe y
with row 3, and row 3's Source.Priority is lower than row 1.

Help!?

Jul 23 '05 #1
5 4572
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,
ItemUniversalKe y CHAR(10),
Price int )

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

INSERT INTO [Item]( ItemID, ItemSourceID, ItemUniversalKe y, Price )
values ( 1, 2, '[key_one]', 15 )
INSERT INTO [Item]( ItemID, ItemSourceID, ItemUniversalKe y, Price )
values ( 2, 2, '[key_two]', 25 )
INSERT INTO [Item]( ItemID, ItemSourceID, ItemUniversalKe y, 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.ItemUniversal Key, 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.goo glegroups.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
ItemUniversalKe y uniqueidentifie r
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 ItemUniversalKe y, 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 ItemUniversalKe y
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, ItemUniversalKe y, 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.ItemUniversal Key, i.Price
FROM [Item] i
INNER JOIN [Source] s ON i.ItemSourceID = s.ItemSourceID
INNER JOIN ( SELECT ItemUniversalKe y, MIN(s.Priority) AS MinPriority
FROM [Item] i
INNER JOIN [Source] s ON i.ItemSourceID = s.ItemSourceID
GROUP BY i.ItemUniversal Key ) p
ON i.ItemUniversal Key = p.ItemUniversal Key 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.ItemUniversal Key, 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.ItemUniversa lKey = I.ItemUniversal Key
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.ItemUniversal Key, 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.ItemUniversal Key = t.ItemUniversal Key )

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, ItemUniversalKe y, 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.ItemUniversal Key, i.Price
FROM [Item] i
INNER JOIN [Source] s ON i.ItemSourceID = s.ItemSourceID
INNER JOIN ( SELECT ItemUniversalKe y, MIN(s.Priority) AS MinPriority
FROM [Item] i
INNER JOIN [Source] s ON i.ItemSourceID = s.ItemSourceID GROUP BY i.ItemUniversal Key ) p
ON i.ItemUniversal Key = p.ItemUniversal Key AND s.Priority =
p.MinPriority
WHERE i.Price < 30

Thanks again ...


Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
2522
by: Joel Thornton | last post by:
I'm having much difficulty figuring out how to write the following query. Please help! I have this table: Event EventId int Primary Key PatientId int SeverityLevel int
3
1629
by: Martin | last post by:
Dear Group I wonder whether you can push me in a direction on how to design the following statement. I'm looking for a SELECT with some tricky ORDER BY. The database table looks like this: MenuID TabText SubTabID TabOrderID ------- ----------- ----------- -----------
3
8042
by: Louis | last post by:
Is there a switch or a setting in Access so that a group by query doesn't return a field name SumOf(original field name)? Especially when you chain multiple queries together you'd get SumOfSumOfSumOf.... Anyone know? TIA.
1
1396
by: Pea | last post by:
I'm working with a system usage database. I want to filter out repetitive logins. The query I have retrieves data like this: USER_DATE USER_TIME1 USER_USERID USER_ACCOUNT 10/01/2004 19:56 y708ga27 The Capital Group 10/01/2004 19:58 y708ga27 The Capital Group 10/01/2004 19:59 y708ga27 The Capital Group 10/01/2004 20:19 y708ga27 The Capital Group In a case like this - with consecutive logins for the same user, if
6
17932
by: Tony Miller | last post by:
All I have an aggregate query using the function Month & Year on a datereceived field ie: TheYear: Year() TheMonth: Month() These are the group by fields to give me a Count on another field by year & month When I try to place a date filter 'Between x And y ' on an expression field
12
25309
by: Bill Moran | last post by:
Hey all. I've hit an SQL problem that I'm a bit mystified by. I have two different questions regarding this problem: why? and how do I work around it? The following query: SELECT GCP.id, GCP.Name FROM Gov_Capital_Project GCP,
22
1750
by: graham.parsons | last post by:
Guys, Hopefully someone can help. We have a monitoring program that has threads which start and stop monitoring at various times. There are two tables: THREADLIFECYCLE unique_id
15
5969
by: edouard.spooner | last post by:
Hi, I have a tricky SQL query problem that I'm having probs with. I have a table which resembles something like this Date | Price1 | Price2 | Price3 01 Jan 2006 | 100 | 100 | 100 02 Jan 2006 | 100 | 100 | 100 03 Jan 2006 | 100 | 100 | 100
1
3209
by: claudfs | last post by:
Hey Guys, Ok here is my problem: There is around 350 rows in the db. All the variables $actor_id, $comment_id and $likes_id are all uid's for facebook users Now i want to group and sum how many entries there are of each unique uid
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10870
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10563
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10267
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7804
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6998
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5668
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5845
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4038
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.