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.
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 sample22006 100022006 60022705 100022705 60022745 100022745 600If 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_dimensionFROM ( SELECT product_id, max_dimension, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY max_dimension DESC)RNFROM mytable) s WHERE rn = 1; |
|
|
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. |
|
|
craigwg
Posting Yak Master
154 Posts |
Posted - 2013-07-16 : 17:08:09
|
Thanks to both ideas. I'll try them now.Craig Greenwood |
|
|
|
|
|
|
|