Jump to content
niche

Adding a 2d constraint

Recommended Posts

My nconnectz table also has a price column.

I've figured out how to get the latest datetime:

SELECT nconnectz.item_id 
	  , nconnectz.vendor_id
 	  , nconnectz.datetime2
	FROM ( SELECT item_id   
	  , vendor_id 
      , MAX(datetime2) AS latest 
	FROM nconnectz 
    WHERE item_id IN (' . $in . ') 
	  AND vendor_id IN (' . $in2 . ')
    GROUP
      BY item_id , vendor_id
	) AS subset 
	INNER
	  JOIN nconnectz
        ON nconnectz.item_id   = subset.item_id    
        AND nconnectz.vendor_id = subset.vendor_id               
        AND nconnectz.datetime2 = subset.latest 

What should my thinking be to select the latest datetime with the lowest price?

Edited by niche

Share this post


Link to post
Share on other sites

This produces the lowest price subset of the query in the OP.

Do you see any issues?

SELECT *, MIN(price) AS minprice FROM (
	SELECT id
	  , nconnectz.item_id 
	  , nconnectz.vendor_id
 	  , nconnectz.datetime2
	  , nconnectz.itemandsize
	  , nconnectz.price
	FROM ( SELECT item_id   
	  , vendor_id 
      , MAX(datetime2) AS latest 
	FROM nconnectz 
    WHERE item_id IN (' . $in . ') 
	  AND vendor_id IN (' . $in2 . ')
    GROUP
      BY item_id , vendor_id
	) AS subset 
	INNER
	  JOIN nconnectz
        ON nconnectz.item_id   = subset.item_id    
        AND nconnectz.vendor_id = subset.vendor_id               
        AND nconnectz.datetime2 = subset.latest
    ) AS subset2 
	GROUP
  	  BY item_id , vendor_id 	

 

Edited by niche

Share this post


Link to post
Share on other sites

The first query  correctly selects 7 rows.

The second query correctly selects the lowest prices from based on the 1st query.

How do I join these two queries?  

NOTE: Only difference between the 1st and 2nd query is the first and last line of the 2nd query.

QUERY 1:
 

 

SELECT id
      , nconnectz.item_id
      , nconnectz.vendor_id
      , nconnectz.store
       , nconnectz.datetime2
      , nconnectz.itemandsize
      , nconnectz.pkg_quantity
      , nconnectz.pkg_price
      , nconnectz.price
    FROM ( SELECT item_id
      , pkg_quantity    
      , vendor_id
      , MAX(datetime2) AS latest
    FROM nconnectz
    WHERE item_id IN (7)
      AND vendor_id IN (5,2)
    GROUP
      BY item_id , vendor_id, pkg_quantity
    ) AS subset
    INNER
      JOIN nconnectz
        ON nconnectz.item_id   = subset.item_id    
        AND nconnectz.vendor_id = subset.vendor_id               
        AND nconnectz.datetime2 = subset.latest
        AND  nconnectz.pkg_quantity = subset.pkg_quantity
    ORDER BY item_id ASC , vendor_id ASC, pkg_quantity ASC

QUERY 2:
 


    SELECT MIN(price) AS minprice FROM (
    SELECT id
      , nconnectz.item_id
      , nconnectz.vendor_id
      , nconnectz.store
       , nconnectz.datetime2
      , nconnectz.itemandsize
      , nconnectz.pkg_quantity
      , nconnectz.pkg_price
      , nconnectz.price
    FROM ( SELECT item_id
      , pkg_quantity    
      , vendor_id
      , MAX(datetime2) AS latest
    FROM nconnectz
    WHERE item_id IN (7)
      AND vendor_id IN (5,2)
    GROUP
      BY item_id , vendor_id, pkg_quantity
    ) AS subset
    INNER
      JOIN nconnectz
        ON nconnectz.item_id   = subset.item_id    
        AND nconnectz.vendor_id = subset.vendor_id               
        AND nconnectz.datetime2 = subset.latest
        AND  nconnectz.pkg_quantity = subset.pkg_quantity
    ORDER BY item_id ASC , vendor_id ASC, pkg_quantity ASC
   ) AS subset3 GROUP BY  vendor_id    

 

Edited by niche

Share this post


Link to post
Share on other sites

You should be able to list all of the columns you want to select, including the minimum price, and group by everything except the price.

Share this post


Link to post
Share on other sites

Thanks jsg. 

1st Situation

SELECT store, vendor_id, item_id,  MIN(price) AS minprice FROM (
...
) AS subset3 GROUP BY vendor_id

Looks like all I need is to GROUP BY vendor_id to get two rows returned  store,  vendor_id, item_id, minprice (I expected two rows)

However, when I add id to the SELECT

2nd Situation

SELECT id, store, vendor_id, item_id,  MIN(price) AS minprice FROM (
...
) AS subset3 GROUP BY vendor_id

I get the same data I received in the 1st Situation except the ids are wrong

What do you think?

EDIT:

datetimes don't correspond to the min price either

EDIT 2:

I'm back to returning 7 rows when my GROUP BY list = my SELECT list (less minprice)

SELECT id, datetime2,store, vendor_id, item_id,  MIN(price) AS minprice FROM (
...
) AS subset3 GROUP BY id, datetime2,store, vendor_id, item_id

 

 

Edited by niche

Share this post


Link to post
Share on other sites

That's right, you have to group by any and every column that is not part of an aggregate function.

Share this post


Link to post
Share on other sites

OK.  I'm following you.

The core of my query is 26 lines (more or less).

So,  you're saying that sql needs 26 lines (more or less) to zero - in on each column and get the precise data i want.

So, 8 columns means a query of 200 lines (more or less).

Right?

Share this post


Link to post
Share on other sites

I'm not sure where you're going with that.  If you want to get all of the data including the minimum price (this assumes that all of the values in the rows are the same except the price), then you just group by all the other columns like you showed:

SELECT id, datetime2,store, vendor_id, item_id,  MIN(price) AS minprice FROM (
...
) AS subset3 GROUP BY id, datetime2,store, vendor_id, item_id

Share this post


Link to post
Share on other sites

They're all potentially different.

In this case, the first pass returned 7 rows and 8 columns.  There will always be varying variability in each column.

I suppose my big decision is how much I want to do with SQL and how much with PHP.

Seems to me PHP is easier to work with than SQL after I use SQL to whittle down 100,000 rows down down to 7.

This topic is about how to take 7 rows down to 2 with columns of varying variability AND  pushing my SQL experience in an effort to pay down and avoid code debt..

Just by changing the ORDER BY I can prep an array for targeting in a loop to zero in on those 2 rows.

I know every situation is different. 

How did you strike the balance between potentially long queries in a model and a few more PHP lines in a view as you became a guru?  

Share this post


Link to post
Share on other sites

You just find a balance that works.  Early on I used to run queries in loops, for example, because it made sense to do that, but that doesn't scale.  It turned out that instead of looping through each row and doing additional queries, I could use a more complex query with a join or something else where I could eliminate the queries inside the loop and just return everything I needed to start with.  That kind of thing obviously scales much better than looping over the data and doing additional queries, but the queries get a lot more complicated.  Then you can use the database server to analyze or explain a query to see how it's going to execute it, and that might influence how you define indexes and keys on those tables to speed things up.  The right indexes can reduce the speed of a query by several orders of magnitude.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×