SQL query with Union - SQL Server Forums

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

Other Forums

MS Access

SQL query with Union

Author

Topic

SunnyBoy
Starting Member

3 Posts

Posted - 2011-11-07 : 12:47:49

Hi! I have 2 tables sharing same 2 columns and remaining different columns. I want to create a new table using a SELECT query that includes only 1 row from either table with the lowest value for that group of items.

For example,

Table A Table B
--------- -----------
color value color value
red 1 red 4
red 2 yellow 2
red 3 blue 1
yellow 1
yellow 2
blue 2

Result wanted:
Table C
----------
color value
red 1
yellow 1
blue 1

I need to group by color and new table includes one row with the lowest value only. Could someone help me with the SQL for this.
Thanks in advance,
Sunny

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-07 : 14:20:54

SELECT Color, MIN(MIN_Value) AS Value
SELECT Color, CASE WHEN a.value < b.value THEN a.value ELSE b.Value End AS MIN_Value
FROM TableA a JOIN TableB b ON a.Color = b.Color) AS XXX
Group BY Color

Is this a homework assignment...what have you tried already?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/