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'

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

noonches posted:

I have a table of survey submission answers set up like this,

where each number in the answers matches up to a question that they said yes to.

I think you're better off having a unique record per survey per answer. Then you would only need to do a COUNT(*) WHERE answer_id = 1 to get what you're looking for.

# ¿ Jan 8, 2008 21:27

Adbot: ADBOT LOVES YOU

# ¿ May 4, 2024 05:32

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

noonches posted:

Unfortunately, I did not set up the table or the framework I'm using for this. So I can't change the way the table is set up, and I can't use a PHP script to tear it apart and get the count because I need to feed a single query into a class that spits out a styled html table for the website. Is this as hard as it seems to me, or am I just having an off day?

am I any closer with this?:
SUM(IF(find_in_set(1, answers),1,0))

I don't have access to MySQL to test it out, but it seems like you could do a COUNT(*) with WHERE find_in_set(1, answers) > 0. I don't know if what you wrote would work, or how it would compare performance-wise.

# ¿ Jan 8, 2008 22:02

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

You shouldn't have to join to your questions table, that's probably why you're having to do the group by.

# ¿ Jan 8, 2008 23:48

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

So, I have a site where users can make predictions on events.

Users can be grouped into leagues.

When I calculate the score for a league, I only want to take the 15 top-scoring users from the league and total only their scores to store as the league's score. (to prevent leagues with <20 users having zero chance to rank against a league with 50+ users)

code:

select
	l.id,
	SUM(points) as total_points,
	SUM(num_correct) as total_num_correct
from
	fw_league l
inner join
	(
		select
			TOP 15
			t.points,
			t.num_correct
		from
			full_fw_user u
		inner join
			fw_user_total t
		on
			t.fw_user_id = u.fw_user_id	
		[b]where
			u.fw_league_id = l.id[/b]
		order by
			t.points DESC	
	) AS tempTable
on
	tempTable.fw_league_id = l.id

The bolded part seems to be the problem - I guess the table with alias l isn't accessible.

I got around it by just getting a list of league IDs, and looping over that and only summing up the results a league at a time, but I'd prefer to have it all in one query. Anyone have any ideas?

# ¿ Jan 9, 2008 17:40

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

imBen posted:

By the way, I don't think your inner join works at all. Your inner join selects 15 points... unless I'm reading something wrong. I probably am.

Hm. The only reason why I'm doing it that way is because I need to only get the top 15 scorers (so I need to order by DESC along with the TOP 15) - and then I have to sum them in the main query.

Maybe if I put the tempTable in the FROM and then join that to the league table, I could get it to work.

Since I can just loop over the items, it's not a huge issue - and these are cached totals so it's not like I'm too concerned about efficiency - I'd just like to learn the way to do that. I haven't done too much SQL like this before, so I have a hard time visualizing what I need to do.

edit - here's the SQL we're talking about

code:

select
	l.id,
	SUM(points) as total_points,
	SUM(num_correct) as total_num_correct
from
	fw_league l
inner join
	(
		select
			TOP 15
			t.points,
			t.num_correct
		from
			full_fw_user u
		inner join
			fw_user_total t
		on
			t.fw_user_id = u.fw_user_id	
		where
			u.fw_league_id = l.id
		order by
			t.points DESC	
	) AS tempTable
on
	tempTable.fw_league_id = l.id

I'm trying to select the scores of the top 15 users in each league and get that total per league.

cletus42o fucked around with this message at 02:46 on Jan 10, 2008

# ¿ Jan 10, 2008 02:43

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

Victor posted:

If you give me sample data (more rows than would be displayed in your desired output) and the desired output, I'll give you a SQL statement that works. Or I'll be really amusing in my attempt.

Let's assume instead of summing the top 15 users, we'll just sum the top 5. That makes this test data considerably easier to type out.

code:

[b]fw_league[/b]
id    
-------------------------
1
2

[b]full_fw_user[/b]
fw_user_id    fw_league_id
---------------------------
1       	1
2		1
3		1
4		1
5		1
6		1
7		1
8		1
9		2
10		2
11		2
12		2
13		2
14		2
15		2
16		2
17		2
18		2
19		2
20		2

[b]fw_user_total[/b]
fw_user_id	points	num_correct
-------------------------------------
1		10		5
2		20		6
3		30		7
4		40		8
5		50		9
6		25		6
7		35		8
8		60		10
9		80		11
10	        30		6
11		50		7
12		10		2
13		45		8
14		20		3
15		70		8
16		40		5
17		15		3
18		0		0
19		35		5
20		30		5

So the output I want is -
fw_league_id	sum_points	sum_correct
---------------------------------------
1	      60+50+40+35+30    10+9+8+8+7
2	      80+50+45+70+40	11+7+8+8+5

So I need the top 5 scoring users (ordered first by points, then by num_correct) to be summed up for each league.

# ¿ Jan 10, 2008 16:26

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

Nice, thanks. I will see if I can get that to work. Took me a second to realize what was going on until I saw the "start reading here."

edit - Actually, I'm still not really sure how to use this. Could you show me what I should only need to stick into QA to get the proper results, if you don't mind? SQL 2000, by the way. I also don't actually need a string showing "x+x+x+x+x", I was only doing that to show you what numbers were being summed up.

Okay, I think I have it all properly entered.. but it's getting caught on row_number() - what function is this? Is there a 2000 equivalent?

cletus42o fucked around with this message at 17:33 on Jan 10, 2008

# ¿ Jan 10, 2008 17:21

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

So I was able to write this based on some advice I got elsewhere.. (couldn't get your SQL to work, Victor, since row_number() doesn't seem to have any equivalent in 2000 - thanks though!)

code:

SELECT
	fw_league_id,
	SUM(points) AS total_points,
	SUM(num_correct) AS total_num_correct
FROM
	(
		SELECT
			u.name,
			u.fw_league_id,
			d.ranknum,
			t.points,
			t.num_correct
		FROM
			full_fw_user u
		INNER JOIN
			fw_user_total t
		ON
			u.fw_user_id = t.fw_user_id
		INNER JOIN
			(
				SELECT
					a.fw_user_id,
					COUNT(*) AS ranknum
				FROM
					(
						SELECT
							u.fw_user_id,
							u.fw_league_id,
							t.points,
							t.num_correct
						FROM
							full_fw_user u
						INNER JOIN
							fw_user_total t
						ON
							t.fw_user_id = u.fw_user_id
					) AS a
				INNER JOIN
					(
						SELECT
							u.fw_user_id,
							u.fw_league_id,
							t.points,
							t.num_correct
						FROM
							full_fw_user u
						INNER JOIN
							fw_user_total t
						ON
							t.fw_user_id = u.fw_user_id
					) AS b
				ON
					(
							a.fw_league_id = b.fw_league_id
						AND
							[b]a.points <= b.points[/b]
					)
				GROUP BY
					a.fw_user_id
				HAVING COUNT(*) <= 15
			) AS d
		ON
			u.fw_user_id = d .fw_user_id
	) AS t
GROUP BY
	fw_league_id

This works, but it's only ordering by POINTS DESC. I would like for it to order first by points DESC, then by num_correct DESC. The part that's "ordering" it by points is bolded. Anyone have any ideas?

# ¿ Jan 10, 2008 21:11

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

Victor posted:

The above can be simplified -- I'm too lazy and tired.

Thanks for the help, dude! I appreciate it.

# ¿ Jan 10, 2008 23:02

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

minato posted:

In the past I've implemented Option 1, like a good little database normalizer. However the clunkiness of doing so has led me to want to implement Option 2. The rest of my team (not very DB-design-savvy) is advocating Option 3, which to me is right out because it would take god himself to pry FK constraints from my dead hands.

I've always used option 3, but then I never use constraints in my database - I handle all of that in my application. But maybe I should rethink that?

But I don't know, I've never had problems with it. The most common way I've used option 3 is in a "files" table, where a file can be attached to any other item in the system. I have the file table itself, which only stores info pertaining to the file, then a table I usually call file_item_jn - the join table has the file ID and two fields called foreign_table and foreign_key.

So I haven't regretted it in the places I've used it.

But then I did develop another system where I have separate join tables for each "type" - and then I have a view that unions them all and creates the type field for me (so a "virtual option 3"), since there are places where I have to provide a snapshot of all the data at once.

# ¿ Jan 16, 2008 14:35

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

SQL Server 2000

I want to run a regular expression on a column when comparing it to text entered through a search form.

WHERE REREPLACE(fieldName, '<[^>]*>', '') LIKE '%#searchKeyword#%'

Basically to do a comparison on a field that may have HTML in it. I want to strip the HTML out as I do the comparison.

# ¿ Oct 2, 2009 17:04

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

I'm attempting to migrate a database from SQL Server 2000 to 2008.

I restored the database on 2008 with no problem.

Suddenly, a lot of my queries are returning results in a totally different order than in 2000.

Here is an example of a query exhibiting this problem:

code:

DECLARE @id TABLE (
	id INT,
	row_number INT IDENTITY( 1, 1 )
 );

DECLARE @row_count INT;

insert into @id	
(
	id
)
(
	select
		t.id
	from
(
	select TOP 100 PERCENT
		m.id
	from
		message m
		
	where
		m.foreign_table = 'user' AND
		m.foreign_key = 2 AND
		m.type = 'USER.Mail' AND
		m.deleted = 0
			
	ORDER BY
		m.date_created DESC
	) AS t
 );
 
SET @row_count = (
	select
		COUNT( * )
	from
		@id
 );
 
select
	m.id,
	m.name,
	m.description,
	m.siteuser_id,
	u.id AS fw_user_id,
	su.name AS siteuser_name,
	m.parent_message_id,
	m.viewed,
	m.deleted,
	m.date_created,
	i.row_number,
	( @row_count ) AS row_count
from
	message m
inner join
	siteuser su
on
	su.id = m.siteuser_id
inner join
	fw_user u
on
	u.siteuser_id = su.id
inner join
	@id i
on
	(
		m.id = i.id AND
		i.row_number >= 1 AND
		i.row_number < 51 
	)
	
order by
	i.row_number

As far as I can tell, SQL 2008 is completely ignoring the ORDER BY in my temp table. (the "ORDER BY m.date_created DESC")

I don't have a problem rewriting this stuff, I'm just trying to figure out what exactly changed in SQL's behavior.

Anyone have any ideas? It's absolutely possible this happened between 2000 and 2005, I haven't tried restoring it to a 2005 DB to check yet.

edit - Looks like it may have to do with the TOP 100 PERCENT. Don't ask me why that's being used.

cletus42o fucked around with this message at 23:15 on May 27, 2010

# ¿ May 27, 2010 23:13

Adbot: ADBOT LOVES YOU

# ¿ May 4, 2024 05:32

cletus42o: Apr 11, 2003; Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.; College Slice

Sprawl posted:

Why would you even attempt do that kind of a join that way? All it looks like its doing is sniping off the top 50 mails it really doesn't need to be that hard.

I modifed the SQL on my own before pasting it here, it's used for pagination, so the 1 to 51 changes based on where you're at. Not to say that this is still the best way to do it - it's just how a lot of the queries for pagination are written in this codebase. So to change it would be acceptable if needed, but if not, I'd rather not.

But I just found that apparently this was a bug that the ORDER BY isn't seen when using TOP 100 PERCENT, and there is a hotfix available. Not sure why our automatic updating didn't pick up this update, but we've got a request in to have the hotfix applied. I'll try it again once it gets patched up, and hopefully that'll have taken care of it. Wasn't really sure how to Google it at first, until I noticed that changing it to TOP 1000 from TOP 100 PERCENT seemed to do the trick - so then I Googled "select top 100 percent SQL 2008" and the issue came up on the first page of results.

edit - This was the first result from Google, if anyone is interested.

http://blog.sqlauthority.com/2009/11/24/sql-server-interesting-observation-top-100-percent-and-order-by/

It sounds like using the TOP 100 PERCENT with an ORDER BY isn't exactly a best practice, but again, I'd rather get it working first, rewrite later.

cletus42o fucked around with this message at 23:34 on May 27, 2010

# ¿ May 27, 2010 23:31

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