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'

Land Mime: Jul 13, 2007; Love never blows up and gets killed

This seems...wrong somehow, but creating an index on `timestamp` will make your query return the correct result. At least on MySQL 5.

edit: I mean I get why it works, it just seems to me that an index shouldn't affect the result of a query.

Land Mime fucked around with this message at 06:01 on Dec 8, 2007

# ¿ Dec 8, 2007 05:58

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 17:19

Land Mime: Jul 13, 2007; Love never blows up and gets killed

Victor posted:

code:

select  timestamp = max(timestamp),
        search
from    searches
where userid = 1
group by search
order by max(timestamp)
limit 2

This also returns the incorrect result (at least on mysql 5). I think you might just have to chalk this up to a mysql bug since sqlite and postgres seem to do this correctly. Create the index and, ideally, compare database versions somewhere and die if its not your current one.

# ¿ Dec 8, 2007 17:53

Land Mime: Jul 13, 2007; Love never blows up and gets killed

fletcher posted:

Hmm, I tried this, but every time I do a SELECT FOUND_ROWS() it always comes back with 1, even though the query where I put SQL_CALC_FOUND_ROWS into returns > 1 rows

If your not already, try using the command line client instead of something like mysql query browser.

# ¿ Dec 28, 2007 00:01

Land Mime: Jul 13, 2007; Love never blows up and gets killed

Bitruder posted:

I have a table called "types" and a table called "items". Each item row is linked to a type row via a type ID number. I want to select every row in "types" and add on a column that returns the number of items that use that type. How can I do this? I've been experimenting with subqueries and joins but I can't get anything working.

Thanks! (I'm using mySQL 4.1)

You're looking for the GROUP BY and COUNT keywords. Your query would probably look something like

code:

SELECT `types`.`id`, COUNT(`items`.`id`)
FROM `types`
LEFT JOIN `items` ON `types`.`id` = `items`.`type_id`
GROUP BY `type_id`;

# ¿ Jan 1, 2008 23:30

Land Mime: Jul 13, 2007; Love never blows up and gets killed

I know nothing about DTS or SQL Server but most databases have some sort of 'VACUUM' command that will reclaim unused space.

# ¿ Sep 27, 2008 16:40

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 17:19

Land Mime: Jul 13, 2007; Love never blows up and gets killed

Numbnuts posted:

code:

CREATE TABLE tb_rltnshp_type
(
	rltnshp_type_cd  CHAR(3)  NOT NULL ,
	rltnshp_type_desc  VARCHAR(20)  NOT NULL  ,
	PRIMARY KEY (rltnshp_type_cd),
        UNIQUE (rltnshp_type_cd)
) WITH (OIDS=FALSE)
;

Someone please tell me I'm on the right track.

You're on the right track, but saying PRIMARY KEY is the same as saying UNIQUE and NOT NULL. What version of postgres are you using? I think WITH (OIDS=FALSE) is the default for more recent versions.

code:

CREATE TABLE tb_rltnshp_type
(
	rltnshp_type_cd  CHAR(3)  PRIMARY KEY ,
	rltnshp_type_desc  VARCHAR(20)  NOT NULL
)
;

# ¿ May 31, 2009 17:20

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