SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with some kind of join or intersect thing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

craigwg
Posting Yak Master

USA
154 Posts

Posted - 07/16/2013 :  16:07:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 07/16/2013 :  16:29:46  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

406 Posts

Posted - 07/16/2013 :  16:40:30  Show Profile  Reply with Quote
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

USA
154 Posts

Posted - 07/16/2013 :  17:08:09  Show Profile  Reply with Quote
Thanks to both ideas. I'll try them now.

Craig Greenwood
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000