group by

Can you please tell me the difference between saying

Select min(x),y

from table

group by y

_

Select x,y

from table

group by x,y

having x = min(x)

Bob:

Examine this:

set nocount on

declare @.table table (x integer, y integer)
insert into @.table values (1,1)
insert into @.table values (2,1)
insert into @.table values (2,1)
insert into @.table values (2,3)
insert into @.table values (3,4)

-- Can you please tell me the difference between saying

Select min(x),y

from @.table

group by y

-- _

Select x,y

from @.table

group by x,y

having x = min(x)

-- -
-- Output
-- -

-- y
-- -- --
-- 1 1
-- 2 3
-- 3 4

-- x y
-- -- --
-- 1 1
-- 2 1
-- 2 3
-- 3 4

Look in particular at the values where y=1. In the first query since you group by Y only and you select MIN(X) both ordered pairs (2,1) are eliminated from the set because x=2 > x=1.

In the second example the query is now grouped by x,y. In this case all unique ordered pairs (x,y) are retained by the grouping; however, the duplicate ordered pair (2,1) is eliminated -- as in the first case. In the second example since x=min(x) for all ordered pairs (x,y) the x=min(x) has no impact on selection.

Dave

|||In the first case you get all distinct values of Y each one paired with the min value of the X calculated among the Y-grouped rows

In the second case you are selecting all distinct pairs of rows and then get the one(s) that has the min X value.|||I think the keyword is carlop's "then"; the x=min(x) isn't applied until after the grouping has been applied.|||Use carlops Logic explantion and Mugambo's example for the correct answer!!!!!!|||Just an off-topic word... Bob, your response isn't the answer to the question. carlops and mugambo's posts should be marked as the answer(s) if that's what answered your question.|||

Did not know that you could mark 2 answers....

this better