SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Aredna: Mar 17, 2007; Nap Ghost

I believe your first ELSE should be an ELSEIF (or ELSE IF - can't remember exact syntax)

# ? Jun 9, 2008 17:10

Adbot: ADBOT LOVES YOU

# ? May 24, 2024 22:40

indigoe: Jul 29, 2003; gonna steal the show, you know it ain't no crime

I have a table that holds data like this:

code:

name | score  |
---------------
A    |1       |
A    |2       |
A    |3       |
B    |1       |
C    |1       |
C    |2       |
D    |1       |
E    |1       |

I want to select distinct names with the highest score for each name.

code:

name | score  |
---------------
A    |3       |
B    |1       |
C    |2       |
D    |1       |
E    |1       |

I've seen people recommend using GROUP BY name ORDER BY MAX(score) for a similar problem but that doesn't work. It could be something really simple, maybe not. Any ideas?

# ? Jun 10, 2008 04:22

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

select max(score) from table group by name order by name, untested

# ? Jun 10, 2008 04:39

indigoe: Jul 29, 2003; gonna steal the show, you know it ain't no crime

Thanks, that was a bit of a :doh:

moment for me.

# ? Jun 10, 2008 05:01

FatCow: Apr 22, 2002; I MAP THE FUCK OUT OF PEOPLE

I have a problem somewhat similar to the guy above me.

code:

mysql> select * from profile where profileid=0 or profileid=2 order by paramnameid;
+-----------+-------------+------------+
| profileid | paramnameid | paramvalue |
+-----------+-------------+------------+
|         0 |           3 | false      |
|         2 |           3 | 4          |
|         0 |           5 | false      |
|         2 |           9 | 1          |
|         0 |           9 | 1          |
|         0 |          10 | customer   |
|         2 |          10 | 3          |
|         2 |          11 | 5          |
|         0 |          21 | e164       |
|         2 |          21 | 6          |
|         0 |          22 | 0xFFFFFFFF |
|         2 |          24 | 2          |
|         0 |          24 | failover   |
|         0 |          25 | 0xFFFFFFFF |
|         0 |          26 | none       |
+-----------+-------------+------------+
15 rows in set (0.00 sec)

The query I would like would take the all the values from profileid=0 unless it is "overridden" by the requested profileid. For example the above, if requesting profileid 2, should be reduced to:

code:

mysql> select * from profile where magic_happens();
+-----------+-------------+------------+
| profileid | paramnameid | paramvalue |
+-----------+-------------+------------+
|         2 |           3 | 4          |
|         2 |           9 | 1          |
|         2 |          10 | 3          |
|         2 |          11 | 5          |
|         2 |          21 | 6          |
|         0 |          22 | 0xFFFFFFFF |
|         2 |          24 | 2          |
|         0 |          25 | 0xFFFFFFFF |
|         0 |          26 | none       |
+-----------+-------------+------------+
9 rows in set (0.00 sec)

the current query I have looks like:

code:

SELECT *
FROM   PROFILE
WHERE  PROFILEID = 0
       AND PARAMNAMEID NOT IN (SELECT PARAMNAMEID
                               FROM   PROFILE
                               WHERE  PROFILEID = 2)
UNION 
SELECT *
FROM   PROFILE
WHERE  PROFILEID = 1;

Which seems like an incredibly hackish way to do it to me but nothing has stood out at me as a more elegant way to format the query. Speed is all that really matters, right now on one of my boxes it takes on average 2ms to complete over 10k iterations randomly selecting from 1.3 unique profiles.

RDBMS is MySQL 5.0, if there is something 5.1 gives over 5.0 I would be willing to upgrade.

# ? Jun 10, 2008 05:05

indigoe: Jul 29, 2003; gonna steal the show, you know it ain't no crime

After some further tinkering it seems my early example wasn't very good, and the problem is a bit more complicated. Let's say I have a burger designer software with 2 tables, burger and burger_ingredients. It allows me to create different variations of burgers and compare how successful they are.

code:

|id | burger_name | burger_version | burger_sales |
---------------------------------------------------
| 1 | cheese      | 1              | 100          |
| 2 | cheese      | 2              | 200          |
| 3 | cheese      | 3              | 230          |
| 4 | ham         | 1              | 421          |


| burger_id | ingredient | quantity |
-------------------------------------
| 2         | bun        | 1        |
| 2         | meat       | 1        |
| 2         | cheese     | 1        |

I want to join these tables and list all ingredients for the must current variations. Now, I was able to get it to work like this, but when I tried to create a view, it complained that "#1349 - View's SELECT contains a subquery in the FROM clause"

code:

SELECT
	b.*, i.*
FROM burger b, burger_ingredient i,
	(SELECT MAX(burger_version) AS version, burger_id FROM burger GROUP BY burger_name ORDER BY burger_name) bv
WHERE 1
AND bv.burger_id = b.burger_id
AND bv.version = b.version
AND b.id = i.burger_id

This is MySQL 5.0.37.

# ? Jun 10, 2008 08:06

npe: Oct 15, 2004

Maybe MySQL doesn't allow inline views inside your literal views? Maybe you can create that subquery as another view and reference it that way... Seems silly though.

# ? Jun 10, 2008 13:00

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

MySQL does not support subqueries in the from clause within a view

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

# ? Jun 10, 2008 13:34

insidion: Oct 26, 2004; I'm an evil wizard who just mastered the secrets of space-time... or something!

I'm trying to create a Trigger that allows only one bit within a column to be true.

This is painfully simple but I haven't worked with Triggers before; is there a good guide out there and could someone help me with this specific one?

The thing I'm running in to, mainly, is how do I grab the id of the row which had it's bit was changed so I can set the rest to false?

I have this right now

code:

CREATE TRIGGER Tg_Change_hasPriority 
   ON  dbo.Events 
   AFTER INSERT, UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
		
END

# ? Jun 10, 2008 23:03

indigoe: Jul 29, 2003; gonna steal the show, you know it ain't no crime

yaoi prophet posted:

Maybe MySQL doesn't allow inline views inside your literal views? Maybe you can create that subquery as another view and reference it that way... Seems silly though.

I'm going to do this unless I come across a better solution. Thanks!

# ? Jun 10, 2008 23:32

Aredna: Mar 17, 2007; Nap Ghost

insidion posted:

I'm trying to create a Trigger that allows only one bit within a column to be true.
...

I'm not familiar with triggers, but when you write the logic to check if a single bit is set this page will probably be useful:

http://www.cs.utk.edu/~vose/c-stuff/bithacks.html#DetermineIfPowerOf2

# ? Jun 10, 2008 23:49

insidion: Oct 26, 2004; I'm an evil wizard who just mastered the secrets of space-time... or something!

Aredna posted:

I'm not familiar with triggers, but when you write the logic to check if a single bit is set this page will probably be useful:

http://www.cs.utk.edu/~vose/c-stuff/bithacks.html#DetermineIfPowerOf2

Thanks, it's a column of SQL Bits though which is essentially a boolean not a field of bits like you're thinking. I'm fairly solid with bit fields, but I'll keep that around for brushing up

I found this too for anyone else who wants to figure out triggers:
http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/1/

That pretty much solved it for me.

# ? Jun 11, 2008 00:16

Stephen: Feb 6, 2004; Stoned

I'm trying to duplicate the 'refined search' feature that eBay uses on its search pages. It will display your list of searched results, and then suggest other searches with their result counts along the side.

Doing this is easy, but doing it efficiently doesn't appear to be simple. Obviously I can do a query for each result count to my database based on the user's search criteria, but that means I have to query my entire table once for each search refinement option.

Is there any way to do this in MySQL without querying the table 20+ times?

# ? Jun 11, 2008 16:43

Puddleboy: Feb 15, 2008

Stephen posted:

I'm trying to duplicate the 'refined search' feature that eBay uses on its search pages. It will display your list of searched results, and then suggest other searches with their result counts along the side.

Doing this is easy, but doing it efficiently doesn't appear to be simple. Obviously I can do a query for each result count to my database based on the user's search criteria, but that means I have to query my entire table once for each search refinement option.

Is there any way to do this in MySQL without querying the table 20+ times?

I'd try storing results in temp tables and querying those:

I search for X: store in #tableX
Refine search X+Y: search #tableX for Y: store in #tableXY
Refine search X+Y+Z: search #tableXY for z: store in #tableXYZ

Then just do a count on the temp tables and destroy them.

That way, your first search might return, say, 500 records out of 50k. Your next searches are only searching those 500 records, or less (if you are adding refined search results like X + Y + Z and not just, X + Y or X + Z, or X + A, etc.)

# ? Jun 11, 2008 17:35

iamstinky: Feb 4, 2004; This is not as easy as it looks.

Anyone have suggestion for a way to mimic MINUS in mysql? I frequently need to diff two result sets and nothing I have found online is usable in a production environment due to the immense amount of time it seems to take.

# ? Jun 11, 2008 17:48

Stephen: Feb 6, 2004; Stoned

Puddleboy posted:

I'd try storing results in temp tables and querying those:

I search for X: store in #tableX
Refine search X+Y: search #tableX for Y: store in #tableXY
Refine search X+Y+Z: search #tableXY for z: store in #tableXYZ

Then just do a count on the temp tables and destroy them.

That way, your first search might return, say, 500 records out of 50k. Your next searches are only searching those 500 records, or less (if you are adding refined search results like X + Y + Z and not just, X + Y or X + Z, or X + A, etc.)

This is a great idea! Thanks.

Is querying temp tables generally faster or is it just because there are less rows to query each time?

# ? Jun 11, 2008 17:53

Puddleboy: Feb 15, 2008

Stephen posted:

This is a great idea! Thanks.

Is querying temp tables generally faster or is it just because there are less rows to query each time?

The only reason this is faster is because of the fewer number of rows. A standard table can be searched much faster if you index it, than a same-data temporary table (as far as I know).

# ? Jun 11, 2008 18:14

Stephen: Feb 6, 2004; Stoned

I've got another question, unfortunately.
Table: items
Columns: id, price

Short of hard-coding the values, what would be the easiest way in MySQL to get a count of all the number of items in any price range?

For example:
0-10000 (30 results)
10001-20000 (43 results)
20001-30000 (33 results)
etc...

# ? Jun 11, 2008 22:42

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

Stephen posted:

I've got another question, unfortunately.
Table: items
Columns: id, price

Short of hard-coding the values, what would be the easiest way in MySQL to get a count of all the number of items in any price range?

For example:
0-10000 (30 results)
10001-20000 (43 results)
20001-30000 (33 results)
etc...

Something like this, you may need to play with those numbers a bit to make it so your price_range looks correct

code:

SELECT FLOOR((price + 9999)/10000) * 10 AS price_range_thousands, COUNT(*) AS num_items
FROM items
GROUP BY price_range_thousands

The formula is FLOOR((x+(n-1))/n) * y where x is your field, n is the size of your category, and y is a multiplier for display.

# ? Jun 12, 2008 14:22

Stephen: Feb 6, 2004; Stoned

Begby posted:

Something like this, you may need to play with those numbers a bit to make it so your price_range looks correct
code:
SELECT FLOOR((price + 9999)/10000) * 10 AS price_range_thousands, COUNT(*) AS num_items
FROM items
GROUP BY price_range_thousands
The formula is FLOOR((x+(n-1))/n) * y where x is your field, n is the size of your category, and y is a multiplier for display.

Thanks, that worked perfectly! I've never used the FLOOR or CEILING functions so I would've never thought of that.

# ? Jun 12, 2008 15:22

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

Stephen posted:

Thanks, that worked perfectly! I've never used the FLOOR or CEILING functions so I would've never thought of that.

I never thought of that either. You need to rely on geniuses to think of this crap.

You should check out 'MySQL Cookbook' from oreilly press. There are a poo poo ton of really good solutions in there for these kinds of things, thats where I found this solution in about 2 minutes of thumbing through pages.

# ? Jun 12, 2008 16:30

fansipans: Nov 20, 2005; Internets. Serious Business.

Stephen posted:

I've got another question, unfortunately.
Table: items
Columns: id, price

Short of hard-coding the values, what would be the easiest way in MySQL to get a count of all the number of items in any price range?

For example:
0-10000 (30 results)
10001-20000 (43 results)
20001-30000 (33 results)
etc...

CASE is also nice for mapping values into buckets, which is powerful with GROUP BY. This is helpful when you need more complexity than a simple math formula will provide:

code:

SELECT (CASE WHEN price BETWEEN 0.00 AND 10000.00 THEN '0-10,000'
             WHEN price BETWEEN 10000.01 AND 20000 THEN '10,000-20,000'
             WHEN price = 1000000 THEN 'one MILLION dollars...'
             ELSE '20,000++'
        END) AS price_bucket,
        count(*) AS item_count
FROM items
GROUP BY 1

# ? Jun 12, 2008 17:13

fansipans: Nov 20, 2005; Internets. Serious Business.

double post

# ? Jun 12, 2008 17:13

Squashy Nipples: Aug 18, 2007

fansipans posted:

CASE is also nice for mapping values into buckets, which is powerful with GROUP BY. This is helpful when you need more complexity than a simple math formula will provide:

Wow, extremely cool usage! I have absolutly no use for this right now, but I will allow it to percolate in my brain... Thanks!

# ? Jun 12, 2008 19:28

Stephen: Feb 6, 2004; Stoned

code:

SELECT * 
FROM autos a 
LEFT JOIN (
	SELECT 
		v.auto_id, 
		COUNT(v.id) as view_count 
	FROM views v 
	WHERE v.view_type_id = '3' 
	GROUP BY v.auto_id 
) vc ON vc.auto_id = a.id 
LEFT JOIN ( 
	SELECT * 
	FROM (
		SELECT id, auto_id, photo_format
		FROM photos 
		ORDER BY orderid ASC 
	) as test 
	GROUP BY auto_id 
) p ON p.auto_id = a.id 
LEFT JOIN makes ON a.make_id = makes.id 
LEFT JOIN models ON a.model_id = models.id 
INNER JOIN auto_upsales au ON au.auto_id = a.id 
WHERE (a.auto_deleted='0000-00-00' OR a.auto_deleted >=NOW())
	AND au.upsale_end > NOW() 
	AND au.upsale_id = 1 
ORDER BY vc.view_count ASC

Is there anything I can do to improve the efficiency of this query? The views table is pushing 5 million records, and the photos table is up to near 200 000 so it's really starting to slow down. I have views.auto_id and views.view_type_id both indexed but it's still taking quite awhile.

# ? Jun 13, 2008 19:26

var1ety: Jul 26, 2004

Stephen posted:

code:
SELECT * 
FROM autos a 
LEFT JOIN (
	SELECT 
		v.auto_id, 
		COUNT(v.id) as view_count 
	FROM views v 
	WHERE v.view_type_id = '3' 
	GROUP BY v.auto_id 
) vc ON vc.auto_id = a.id 
LEFT JOIN ( 
	SELECT * 
	FROM (
		SELECT id, auto_id, photo_format
		FROM photos 
		ORDER BY orderid ASC 
	) as test 
	GROUP BY auto_id 
) p ON p.auto_id = a.id 
LEFT JOIN makes ON a.make_id = makes.id 
LEFT JOIN models ON a.model_id = models.id 
INNER JOIN auto_upsales au ON au.auto_id = a.id 
WHERE (a.auto_deleted='0000-00-00' OR a.auto_deleted >=NOW())
	AND au.upsale_end > NOW() 
	AND au.upsale_id = 1 
ORDER BY vc.view_count ASC 
Is there anything I can do to improve the efficiency of this query? The views table is pushing 5 million records, and the photos table is up to near 200 000 so it's really starting to slow down. I have views.auto_id and views.view_type_id both indexed but it's still taking quite awhile.

The query is trying to return, for each make/model, the make, the model, the number of times it has been viewed, one photo, and something from auto_upsales?

# ? Jun 13, 2008 19:45

Aredna: Mar 17, 2007; Nap Ghost

Right off you can change this:

code:

LEFT JOIN ( 
	SELECT * 
	FROM (
		SELECT id, auto_id, photo_format
		FROM photos 
		ORDER BY orderid ASC 
	) as test 
	GROUP BY auto_id 
) p ON p.auto_id = a.id

code:

LEFT JOIN ( SELECT id, auto_id, photo_format
	    FROM photos 
	    ORDER BY orderid ASC 	
) p ON p.auto_id = a.id

and achieve the same result. The group by isn't doing anything because you are selecting other fields that are not in a sum/min/max etc.

What fields are indexed in each of the tables?

Do you need left joins or is the database set up so that if autos.make_id or autos.model_id is in the autos table then makes.id and models.id will exist?

Can you post the execution plan? That may give the most insight into being able to help optimize the query.

# ? Jun 13, 2008 19:48

fansipans: Nov 20, 2005; Internets. Serious Business.

Stephen posted:

code:

...

	FROM (
		SELECT id, auto_id, photo_format
		FROM photos 
		[s]ORDER BY orderid ASC [/s]
	) as test 
...

There's absolutely no reason to include an ORDER BY in a sub-select, you don't need them orderd. All you're doing is causing extra work for the database.

Also, avoid SELECT *, just request the fields you need.

# ? Jun 13, 2008 19:55

var1ety: Jul 26, 2004

Aredna posted:

Right off you can change this:
code:
LEFT JOIN ( 
	SELECT * 
	FROM (
		SELECT id, auto_id, photo_format
		FROM photos 
		ORDER BY orderid ASC 
	) as test 
	GROUP BY auto_id 
) p ON p.auto_id = a.id 
to
code:
LEFT JOIN ( SELECT id, auto_id, photo_format
	    FROM photos 
	    ORDER BY orderid ASC 	
) p ON p.auto_id = a.id 
and achieve the same result. The group by isn't doing anything because you are selecting other fields that are not in a sum/min/max etc.

The GROUP BY will still force the query to return only one row per auto_id in the photos table. The ORDER BY clause in the original sub-select will probably make it return the row for the auto_id having the largest or smallest orderid. Taking this out of the sub-select will probably cripple performance, doing an outer join against a 200k row table.

You gotta feel bad for MySQL users. Without having analytics you really have to bend over backwards to avoid self joins.

# ? Jun 13, 2008 19:58

Stephen: Feb 6, 2004; Stoned

var1ety posted:

The GROUP BY will still force the query to return only one row per auto_id in the photos table. The ORDER BY clause in the original sub-select will probably make it return the row for the auto_id having the largest or smallest orderid. Taking this out of the sub-select will probably cripple performance, doing an outer join against a 200k row table.

You gotta feel bad for MySQL users. Without having analytics you really have to bend over backwards to avoid self joins.

This is exactly what I had to do. MySQL would always return the last orderid instead of the first when I used a group by, despite how I specified the order. Only one photo with the lowest orderid should be joined to each auto.

When I remove the JOINs on the views and photos table the query is done in 0.001 seconds, so I know where the problem is, but unfortunately I don't know any really good way to fix it.

# ? Jun 13, 2008 21:19

Stephen: Feb 6, 2004; Stoned

Well one thing I did that shaved a full second off the query was to remove the JOIN on the photos table and just query for each individual photo within a loop in my PHP. The three queries add up to about 1.5 seconds now instead of 2.2.

# ? Jun 13, 2008 21:23

Aredna: Mar 17, 2007; Nap Ghost

var1ety posted:

The GROUP BY will still force the query to return only one row per auto_id in the photos table. The ORDER BY clause in the original sub-select will probably make it return the row for the auto_id having the largest or smallest orderid. Taking this out of the sub-select will probably cripple performance, doing an outer join against a 200k row table.

You gotta feel bad for MySQL users. Without having analytics you really have to bend over backwards to avoid self joins.

Ahh, my understanding was that if you just groupped by one field, and the other 2 fields would return a data for every row in the table. Is this specific to MySQL or did I just completely misunderstand group by?

# ? Jun 13, 2008 21:23

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

var1ety posted:

You gotta feel bad for MySQL users. Without having analytics you really have to bend over backwards to avoid self joins.

Agreed. I loves me some rank() over (partition by Column, order by OtherColumn).

# ? Jun 13, 2008 21:23

npe: Oct 15, 2004

Aredna posted:

Ahh, my understanding was that if you just groupped by one field, and the other 2 fields would return a data for every row in the table. Is this specific to MySQL or did I just completely misunderstand group by?

This is something that MySQL handles wrong. In most database systems, if you try to access columns that are not explicitly included in your GROUP BY, it will complain. For example, in Oracle:

code:

SQL> select a,b from foo group by a;
select a,b from foo group by a
         *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

This means you always, always have to include all columns as either GROUP BY criteria, or wrapped in an aggregate function in some way.

In MySQL, you can refer to other columns not included in the GROUP BY criteria plainly. Of course the question is, which row are you getting back, and I think the answer is "whichever", but I haven't read any actual MySQL documentation on this subject in years so it may have changed.

# ? Jun 13, 2008 21:44

Stephen: Feb 6, 2004; Stoned

yaoi prophet posted:

In MySQL, you can refer to other columns not included in the GROUP BY criteria plainly. Of course the question is, which row are you getting back, and I think the answer is "whichever", but I haven't read any actual MySQL documentation on this subject in years so it may have changed.

I found that MySQL just returns the last row in whatever order it groups it by, usually the primary key. So in my case I had to sort it by the orderid, and then group it in order to return one row with the result I wanted.

# ? Jun 13, 2008 21:50

npe: Oct 15, 2004

Stephen posted:

I found that MySQL just returns the last row in whatever order it groups it by, usually the primary key. So in my case I had to sort it by the orderid, and then group it in order to return one row with the result I wanted.

The concern here is that it's undefined behavior, and it may appear to work in many circumstances but not in others. A good example from Oracle is where people order by ROWID, hoping to preserve the "natural" ordering of the table. I see this a lot and it's bad because it usually looks like it's working, but it will occasionally not -- Oracle uses ROWID internally and makes no guarantee about table data being in any particular order.

This is the same sort of situation. It would be important to know what the explicit behavior should be, but a quick check of the MySQL docs about GROUP BY doesn't show any mention of this behavior that I can find.

# ? Jun 13, 2008 21:57

Stephen: Feb 6, 2004; Stoned

yaoi prophet posted:

The concern here is that it's undefined behavior, and it may appear to work in many circumstances but not in others. A good example from Oracle is where people order by ROWID, hoping to preserve the "natural" ordering of the table. I see this a lot and it's bad because it usually looks like it's working, but it will occasionally not -- Oracle uses ROWID internally and makes no guarantee about table data being in any particular order.

This is the same sort of situation. It would be important to know what the explicit behavior should be, but a quick check of the MySQL docs about GROUP BY doesn't show any mention of this behavior that I can find.

How do you get around something like this in Oracle? If you aren't allowed to reference columns that aren't in the GROUP BY, how do you output them with the rest of the row? Is this a MySQL habit that I should kick right now before I get into Oracle/SQLServer?

# ? Jun 13, 2008 22:06

var1ety: Jul 26, 2004

Stephen posted:

How do you get around something like this in Oracle? If you aren't allowed to reference columns that aren't in the GROUP BY, how do you output them with the rest of the row? Is this a MySQL habit that I should kick right now before I get into Oracle/SQLServer?

In older versions you would need to self join (get the id and maximum value and then join back to the original table). In newer versions you can use analytics such as row_number() or rank() to compute an ordering which you then filter on.

# ? Jun 13, 2008 22:22

megalodong: Mar 11, 2008

yaoi prophet posted:

This means you always, always have to include all columns as either GROUP BY criteria, or wrapped in an aggregate function in some way.

In MySQL, you can refer to other columns not included in the GROUP BY criteria plainly. Of course the question is, which row are you getting back, and I think the answer is "whichever", but I haven't read any actual MySQL documentation on this subject in years so it may have changed.

MySQL does handle it "wrong" in that it allows you to list any colums you want, but as of the 1999 SQL standard, the only requirement for the non-aggregate columns in the select list is that they must be functionally dependant on whatever's in the GROUP BY list.

# ? Jun 14, 2008 10:14

Adbot: ADBOT LOVES YOU

# ? May 24, 2024 22:40

Casimirus: Mar 28, 2005; Yes.

This is a dumb question, but I've never had it come up before:

In MSSQL is there a way to do

set @agg = (select sum(booya) from table1) + (select sum(booya) from table2)

instead of doing it in two steps? I don't think you can cast each side to the correct datatype or whatever.

# ? Jun 14, 2008 18:53

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »