ansaurus

Question

Answer 1

A:

(Removed incorrect answer)

fizban 2010-08-23 13:38:51

This can't be the same. He is querying #table1 without grouping, there is only a left outer join and no where: it will result in the same number of records as there are in #table1. Your query gets a record by different field3. Aaaaand ... if you select field1 and field2, you *must* also group by these fields. This query wont run at all.

Stefan Steinegger 2010-08-23 14:00:16

@fizban - thanks for your time, but like Stefan said sql won't know what to do with field1 and field2 because there could be multiple values for each value of field3, so which one of those multiple values would be returned?

kralco626 2010-08-23 14:10:48

@kralco626 - You're absolutely right, sorry about that. That might teach me to test my solutions before posting :-)

fizban 2010-08-23 14:23:56

@fizban - haha no problem, i thank you for your try though!

kralco626 2010-08-23 14:31:27

You may want to delete the answer before you get down-voted anymore.

Stefan Steinegger 2010-08-23 14:52:19

Answer 2

+2 A:

SELECT field1, MAX(field4) OVER (PARTITION BY field3) AS field2
FROM  #table1

Though following clarification in the comments...

I think what i really was looking for was one record per field3, the max(field4) and the corresponding field1. And I think the assumption would have to be made that field1 and field3 has a strictly one-many relationship (aka a value of field3 could never have two corresponding values of field1)

... I think this is what you actually need.

WITH cte As
(
SELECT field1, field3,field4,
ROW_NUMBER() OVER (PARTITION BY field3 ORDER BY field4 DESC) AS RN
FROM #table1
)
SELECT  field1, field3,field4
FROM cte
WHERE RN=1

Martin Smith 2010-08-23 14:21:16

Thanks! So this should give me a row for every unique value of field3 right? What would happen in the case that one value of field3 matched up to two values of field1?

kralco626 2010-08-23 14:29:06

I think, to answer my own question, you would get two rows, because the only thing that determines the rows is field3.

kralco626 2010-08-23 14:30:34

This should do exactly the same as your example query. It will return all rows in `#table1` (you have a left join). For each row it will return the maximum value of `field4` corresponding to that rows `field3` value and it will alias that result as `field2`

Martin Smith 2010-08-23 14:32:44

WOW - so 2 things. First, thanks so much because that deff answered my question. Second, thanks so much because you just made me double think what I was doing and realize that my original query was wrong...

kralco626 2010-08-23 15:09:56

I think what i really was looking for was one record per field3, the max(field4) and the corrisponding field1. And I think the assumption would have to be made that field1 and field3 has a strictly one-many relationship(aka a value of field3 could never have two corrisponding values of field1)

kralco626 2010-08-23 15:20:04

So maybe something like Select distinct field1,field3,Max(field4) Over (Partition by field3) as field2 from #table1

kralco626 2010-08-23 15:21:45

ansaurus

tags:

views:

answers:

Convert query to only use table once

related questions