I'm bugged. - 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

SQL Server 2000 Forums

SQL Server Development (2000)

I'm bugged.

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-10 : 11:37:45

I'm

bugged

by a problem I don't have.

It began with a single insect and has grown into a swarm.

If I have a rowset of aggregates

SELECT columna, max(columnb)
from MyTable
WHERE MyConditions
GROUP BY columna

and I want to add additional non-aggregated results to each row of the rowset from another query with different WHERE conditions.....

SELECT Top 1 columnc, columnd, columne
FROM MyTable
WHERE SomeOtherConditionSet
ORDER BY SomeUniqueOrderCondition

Is there a way to generate a recordset of the latter query for each value of columna in the former query and join the two recordsets? Specifically, without resorting to:

SELECT columna, max(columnb),
 (select top 1 columnc from Mytable where SomeOtherConditionSet ORDER BY SomeUniqueOrderCondition) columnc,
 (select top 1 columnd from Mytable where SomeOtherConditionSet ORDER BY SomeUniqueOrderCondition) columnd,
 (select top 1 columne from Mytable where SomeOtherConditionSet ORDER BY SomeUniqueOrderCondition) columne 
from MyTable
WHERE MyConditions
GROUP BY columna

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-10 : 11:46:24

quote:
If I have a rowset of aggregates
SELECT columna, max(columnb)
from MyTable
WHERE MyConditions
GROUP BY columna

and I want to add additional non-aggregated results to each row of the rowset from another query with different WHERE conditions.....

SELECT Top 1 columnc, columnd, columne
FROM MyTable
WHERE SomeOtherConditionSet
ORDER BY SomeUniqueOrderCondition

Is there a way to generate a recordset of the latter query for each value of columna in the former query and join the two recordsets?

Yes, with:


select *
from (
    SELECT columna, max(columnb)
    from MyTable
    WHERE MyConditions
    GROUP BY columna) a
        inner join (
        SELECT columnc, columnd, columne
        FROM MyTable
        WHERE SomeOtherConditionSet) b on a.{} \ b.{})

Edited by - setbasedisthetruepath on 07/10/2003 11:46:50

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-10 : 12:30:24

Sam,

What do you mean by:

quote:

SomeOtherConditionSet

Does that mean a simple predicate?

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-10 : 12:33:33

quote:
Does that mean a simple predicate?

Wouldn't matter.

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-10 : 13:28:53

I forgot to subscribe to this post, so my response is a little slow.

To Brett's question: The first GROUP BY query has some condition set, while the second query which selects TOP 1 has any other (not the same) condition set.

Jonathan, I did a poor job highlighting the constraints and goals of th e 2nd query

quote:
SELECT Top 1 columnc, columnd, columne
FROM MyTable
WHERE SomeOtherConditionSet
ORDER BY SomeUniqueOrderCondition

The above query returns a single row, for a given columna (which I forgot to point out), so it can be joined to the first query ON Q1.columna=Q2.columna. The problem is, it doesn't generate a rowset (each row for a different columna) of all columna values returned in the GROUP BY of the first query.

The solution proposed in your post doesn't take the TOP 1 value for each specific columna.

I'm making mistakes here, so let me throw caution to the wind and risk further credibility damage. I'll thrust onward and say: Here's another simpler statement of the problem that focuses on the rowset generation.

Is there a simpler syntax that will generate the following rowset in the following query (which can then be joined to the first query).

SELECT

(select top 1 columnc from mytable where columna=M1.columna AND mycondition order by myorderby) columnc
(select top 1 columnd from mytable where columna=M1.ccolumna AND mycondition order by myorderby) columnd
(select top 1 columne from mytable where columna=M1.columna AND mycondition order by myorderby) columne

FROM Mytable M1

WHERE Anycondition

GROUP BY columna
Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-10 : 13:53:58

EDIT:
"Joins" in my vernacular does not include correlated subqueries ...

quote:
The solution proposed in your post doesn't take the TOP 1 value for each specific columna.

TOP 1 can never return anything save the same 1 row; you can't create joins that use TOP (n) as a MAX() or MIN() substitute in a GROUP BY.

However, you can write SQL to do this by creating an intermediate rowset containing the primary key of the "TOP 1" row you want, and then joining to that for whatever columns you need.

Edited by - setbasedisthetruepath on 07/10/2003 14:13:18

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-10 : 13:59:29

Thanks. You might want to look at the thread that started me thinking about this if you get a chance.

[url]http://sqlteam.com/Forums/topic.asp?TOPIC_ID=27550[/url]

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-10 : 14:10:32

I did, and as an aside I think it's neat that people such as yourself have the intellectual curiousity to explore problems like these even when they're not personally impacted.

What caught my eye in the earlier thread was the repetitive use of the correlated select top 1 for every column in the row of interest; my suggestion is to create an intermediate rowset that captures the primary key of that row of interest for every "column a" you mention, and then join back to that for every column you want to display. You run 2 queries instead of (n) queries in this way.

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-10 : 14:20:01

Something like that might be possible. If he ever posts back answers to my last question.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-10 : 15:41:50

Yeah,

I forgot about that thread...

He need's to post the table DDL so we can see what he's doing.

I stillt think there is no need to do SELECT (SELECT

When he can get Use derived tables..heck even store stuff un temp tables if it get's too complex (never met a query I didn't like though).

What about:


SELECT columna, Max(columnb), Max(columnc)
  FROM	  Mytable M1 
	, (select top 1 columna, columnc from mytable where mycond order by myorderby) M2 
	, (select top 1 columna, columnd from mytable where mycond order by myorderby) M3 
	, (select top 1 columna, columne from mytable where mycond order by myorderby) M4 
WHERE	  Anycondition 
  AND	  M1.Columna = M2.columna
  AND	  M1.Columna = M3.columna
  AND	  M1.Columna = M4.columna
GROUP BY  columna

Not sure I can see where you're going though...

I mean what's the criteria to pick the other "first" (to use an access term) row. Isn't it arbitrary?

I gues it would help if you told use what type of data you're trying to display and why...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources