advanced select problem

[ngardner]

I need to be able to select the newest listings from a list of skus with the status of 'listed' IF a listing with the same sku, same channel, same account, and status of 'verified' does not exist.

 

table structure

CREATE TABLE `listings` (

  `id` int(11) NOT NULL auto_increment,

  `account_id` int(11) NOT NULL,

  `inventory_sku` varchar(55) NOT NULL,

  `channel_id` int(11) NOT NULL,

  `channel_listing_key` int(11) NOT NULL,

  `status` varchar(55) NOT NULL,

  `cDate` timestamp NOT NULL default CURRENT_TIMESTAMP,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=41 DEFAULT CHARSET=latin1

 

This is what I have so far. It matches all my requirements except for

the "newest" part (which should be based off cdate)

 

SELECT
                       *
                FROM
                        `listings`
                WHERE
                        `account_id` = 1 AND
                        `channel_id` = 2 AND
                        `status` = 'listed' AND
                        `inventory_sku` IN ('10030263ColorRed','10030263ColorGreen','10030263ColorBlue','10030263ColorClear') AND
                        `inventory_sku` NOT IN (
                                SELECT
                                        `inventory_sku`
                                FROM
                                        `listings`
                                WHERE
                                        `account_id` = 1 AND
                                        `channel_id` = 2 AND
                                        `status` = 'verified' AND
                                        `inventory_sku` IN ('10030263ColorRed','10030263ColorGreen','10030263ColorBlue','10030263ColorClear')
                        )
                group by `inventory_sku`
                order by `cDate` desc

 

changing the order by `cDate` to 'asc' isn't the resolution, because thats ordering the results AFTER the group by happens. I'm always getting the OLDEST record instead of the newest record.

 

if I remove the group by, i get all the records for the previously listed skus - which would then require php filtering them out, which I dont want to do.

 

I think a temp table or view might work, but not sure how to do that - and would be best if I didn't have to. (right?)

 

Please help! Thanks!

[ejaboneta]

Sorry, I don't have a solution... but this is very similar to the problem I'm having and no ones answered my post yet! I thought we might work together on it... Have you considered using GREATEST to pick the latest date? I'm not actually sure how to implement it but that's where I'm at with it.

[ngardner]

I think I found a solution, though it might not work in your case.

 

This works if you only need 1 column in the results (i only care about the listing.id).

 

I simply changed

SELECT *

to

SELECT max(id)

and it works great!

 

Final SQL statement....

SELECT
max(id)
FROM
`listings`
WHERE
`account_id` = 1 AND
`channel_id` = 2 AND
	`status` = 'listed' AND
`inventory_sku` IN ('10030263ColorRed','10030263ColorGreen','10030263ColorBlue','10030263ColorClear') AND
`inventory_sku` NOT IN (
	SELECT
		`inventory_sku`
	FROM
		`listings`
	WHERE
		`account_id` = 1 AND
		`channel_id` = 2 AND
		`status` = 'verified' AND
		`inventory_sku` IN ('10030263ColorRed','10030263ColorGreen','10030263ColorBlue','10030263ColorClear')
	)
group by `inventory_sku`
order by `cDate` desc

 

if you need all the columns, but only need 1 row, you can do this...

 

SELECT
*
FROM
`listings`
WHERE
`account_id` = 1 AND
`channel_id` = 2 AND
	`status` = 'listed' AND
`inventory_sku` IN ('10030263ColorRed','10030263ColorGreen','10030263ColorBlue','10030263ColorClear') AND
`inventory_sku` NOT IN (
	SELECT
		`inventory_sku`
	FROM
		`listings`
	WHERE
		`account_id` = 1 AND
		`channel_id` = 2 AND
		`status` = 'verified' AND
		`inventory_sku` IN ('10030263ColorRed','10030263ColorGreen','10030263ColorBlue','10030263ColorClear')
	) AND
`cDate` = (
	SELECT max(cDate) from `listings` WHERE
		`account_id` = 1 AND
		`channel_id` = 2 AND
		`status` = 'listed' AND
		`inventory_sku` IN ('10030263ColorRed','10030263ColorGreen','10030263ColorBlue','10030263ColorClear')
) 
group by `inventory_sku`
order by `cDate` desc

 

If you need all columns and all rows, you'll prob have to do 2 sql statments. First select all Ids, then select all columns that have those Ids.

 

This is where I found my answer: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

[ejaboneta]

Your solution wouldn't help me as not all my "newest" rows are the highest ids... so if you were to somehow enter an item with a date thats before the one thats already there, you'd get the older record because it has a higher id.

 

My solution was subquery using max(date) as a column.