SQL QUERY for a SQLSERVER 2008

Question

I have two tables Product and images. The values in the two tables are given below

SELECT * FROM ProductTable

Structure is (Productid, Categoryid, ProductName)

1   1   Product1
2   1   Product2
3   2   Product2
4   2   Product3
5   2   Product4

SELECT * FROM ImageTable

Structure is (ImageID, ProductID, ImageName)

1   1   Image1
2   1   Image2
3   2   Image3
4   2   Image4
5   4   Image5
6   5   Image6

My query is :

SELECT ImageTable.ProductID, ImageName 
FROM ImageTable 
LEFT JOIN ProductTable ON ImageTable.ProductID=ProductTable.ProductID 
GROUP BY ImageTable.ProductID, ImageName

The result is :

1   Image1
1   Image2
2   Image3
2   Image4
4   Image5
5   Image6

But I want the result as (i.e. the First Imagename of the Productid from the imagetable)

1       Image1
2       IMage3
4       Image5
5       Image6

Is "first" defined as the row with the lowest ImageId value? Because tables, by definition, do not have an order. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 19, 2013 at 6:39

Maxim Zhukov · Accepted Answer · 2013-07-19 06:17:17Z

2

SELECT ImageTable.ProductID, MIN(ImageName)
FROM ImageTable 
LEFT JOIN ProductTable ON ImageTable.ProductID=ProductTable.ProductID 
GROUP by ImageTable.ProductID

answered Jul 19, 2013 at 6:17

Maxim Zhukov

10.2k5 gold badges50 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amit Singh Over a year ago

it sql server you must put column that are in select list either in group by or aggregate function t otherwise it give u error

Devart · Accepted Answer · 2013-07-19 06:27:01Z

2

Really LEFT JOIN with ProductTable is unnecessary. So try this one -

SELECT ProductID, ImageName
FROM (
     SELECT 
            i.ProductID
          , i.ImageName
          , rn = ROW_NUMBER() OVER (PARTITION BY i.ProductID ORDER BY i.ImageName)
     FROM dbo.ImageTable i
) t
WHERE t.rn = 1

Or try this -

SELECT 
       i.ProductID
     , ImageName = MIN(i.ImageName)
FROM dbo.ImageTable i
GROUP BY i.ProductID

Output -

ProductID   ImageName
----------- ---------
1           Image1
2           Image3
4           Image5
5           Image6

edited Jul 19, 2013 at 6:27

answered Jul 19, 2013 at 6:20

Devart

120k23 gold badges172 silver badges189 bronze badges

3 Comments

Himanshu Over a year ago

I just used MIN() without understanding his actual query. +1 for taking time to answer and proving simple queries.

Devart Over a year ago

Thanks. @hims056, can you undelete your answer? I can't up-vote it.

Devart Over a year ago

@hims056, but you can always improve it.

Mechanic · Accepted Answer · 2013-07-19 07:08:15Z

2

SELECT ProductID, MIN(ImageName) 
     FROM 
       ImageTable 
GROUP by ProductID

answered Jul 19, 2013 at 7:08

Mechanic

736 bronze badges

Collectives™ on Stack Overflow

SQL QUERY for a SQLSERVER 2008

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related