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
 Help with some kind of join or intersect thing

Author  Topic 

craigwg
Posting Yak Master

154 Posts

Posted - 2013-07-16 : 16:07:20
I have a table that has product_id and img_size. Some products are in there with multiple records, some only have one. The size is either 'small', 'medium', or 'large'.

I want 1 record for each product and I want the largest image available. But I can't get it. I am trying currently to break them apart as such:


SELECT product_id, max_dimension
FROM mytable
WHERE max_dimension='1000'
GROUP BY 1,2

UNION

SELECT product_id, max_dimension
FROM mytable
WHERE max_dimension='600'
GROUP BY 1,2
ORDER BY 1, 2 DESC


That gets rid of the images that have 8 images of the same size. But my results still show each product_id, most of which have two records, 1000, and 600.

Result sample
22006 1000
22006 600
22705 1000
22705 600
22745 1000
22745 600


If there's a 1000 I want that one, but if not I'm happy with the 600. But I only want one record. How do I do this?


Craig Greenwood

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-16 : 16:29:46
DO it like this. I am assuming that max_dimension is of numeric type. If it is character type, cast it to numeric in the order by clause. If you have more than one image for a given product_id with max_dimension=1000 (or whatever the largest dimension is), and you want to get all of those, use RANK() instead of ROW_NUMBER().
      
SELECT product_id, max_dimension
FROM (
SELECT product_id, max_dimension,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY max_dimension DESC)RN
FROM mytable
) s WHERE rn = 1;
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-07-16 : 16:40:30
Alternative:

select product_id
,max(cast(max_dimension as int))
from mytable
where max_dimension in ('600','1000')
group by product_id
order by product_id


If you leave out the "where" line, you would get largest dimension, no matter what the value.
Go to Top of Page

craigwg
Posting Yak Master

154 Posts

Posted - 2013-07-16 : 17:08:09
Thanks to both ideas. I'll try them now.

Craig Greenwood
Go to Top of Page
   

- Advertisement -