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 »

Charun: Feb 8, 2003

Lancer383 posted:

I think this will work, but it's just off the top of my head:

Won't that put all the hours into the month it went down in? I'm trying to get the hours that are in the next month count towards the next month.

# ? Mar 17, 2008 14:55

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 20:03

var1ety: Jul 26, 2004

Charun posted:

I have a date related problem.

I would create a table of months with their beginning date (00:00:00) and ending date (23:59:59) and join against that. You'd want to get the month the event started in, the event the month ended in, then all the months between there.

The most straightforward solution in Oracle syntax follows. MySQL 5.0 syntax should be very close to this.

code:

select fy_m, sum(days_in_month)*24
  from (select t.a,
               t.b,
               d3.fy_m,
               case
               -- event starts before, ends after
                 when t.a < d3.begin_date and t.b > d3.end_date then
                  d3.end_date - d3.begin_date
               -- event starts before, ends during
                 when t.a < d3.begin_date and t.b between d3.begin_date and
                      d3.end_date then
                  t.b - d3.begin_date
               -- event starts during, ends after
                 when t.a between d3.begin_date and d3.end_date and
                      t.b > d3.end_date then
                  d3.end_date - t.a
               -- starts during, ends during
                 when t.a between d3.begin_date and d3.end_date and
                      t.b between d3.begin_date and d3.end_date then
                  t.b - t.a
               end AS days_in_month
          from foo_down_event t
         inner join foo_month d1 on (t.a between d1.begin_date and
                                    d1.end_date)
         inner join foo_month d2 on (t.b between d2.begin_date and
                                    d2.end_date)
         inner join foo_month d3 on (d3.end_date <= d2.end_date and
                                    d3.begin_date >= d1.begin_date))
 group by fy_m
 order by 1

code:

FY_M	SUM(DAYS_IN_MONTH)*24
2008_M01	13.9997222222222
2008_M02	4

# ? Mar 17, 2008 16:22

Victor: Jun 18, 2004

I'm getting kind of busy, so if you have a problem that hasn't been addressed for several days in this thread, feel free to PM me with a link to the original post, as well as follow-up information from subsequent posts, and create table statements/test data, desired output. Make it easy for me to help you, and I'll do my best.

# ? Mar 17, 2008 17:43

Charun: Feb 8, 2003

var1ety posted:

I would create a table of months with their beginning date (00:00:00) and ending date (23:59:59) and join against that. You'd want to get the month the event started in, the event the month ended in, then all the months between there.

The most straightforward solution in Oracle syntax follows. MySQL 5.0 syntax should be very close to this.

Thank you so very very much.
Here's what I ended up with:

code:

select YearMonth, sum(DaysInMonth) as HoursDown
  from (select wo.DateDown,
               wo.DateUp,
			   wo.ID,
               d3.YearMonth,
               case
               -- event starts before, ends after
                 when wo.DateDown < d3.BeginDate and wo.DateUp > d3.EndDate then
                  addtime(timediff(d3.EndDate, d3.BeginDate), '00:00:01')
               -- event starts before, ends during
                 when wo.DateDown < d3.BeginDate and wo.DateUp between d3.BeginDate and
                      d3.EndDate then
                  timediff(wo.DateUp, d3.BeginDate)
               -- event starts during, ends after
                 when wo.DateDown between d3.BeginDate and d3.EndDate and
                      wo.DateUp > d3.EndDate then
                  addtime(timediff(d3.EndDate, wo.DateDown), '00:00:01')
               -- starts during, ends during
                 when wo.DateDown between d3.BeginDate and d3.EndDate and
                      wo.DateUp between d3.BeginDate and d3.EndDate then
                  timediff(wo.DateUp, wo.DateDown)
               end AS DaysInMonth
          from WorkOrders wo
         inner join MonthDefs d1 on (wo.DateDown between d1.BeginDate and
                                    d1.EndDate)
         inner join MonthDefs d2 on (wo.DateUp between d2.BeginDate and
                                    d2.EndDate)
         inner join MonthDefs d3 on (d3.EndDate <= d2.EndDate and
                                    d3.BeginDate >= d1.BeginDate)) AS foo
group by YearMonth
 order by 1;

and it works perfectly! Do you want plat or archives?

# ? Mar 18, 2008 04:10

var1ety: Jul 26, 2004

Charun posted:

and it works perfectly! Do you want plat or archives?

No thanks, I just contribute for fun.

# ? Mar 18, 2008 16:38

Whilst farting I: Apr 25, 2006

This is relating to a book database, I have no idea how to word this query.

For each genre, list the genre, all of the publishers of that genre, and owner of each publisher. The problem: each of these are in different tables and I don't know how to format this without it looking like total poo poo.

Ideally, it'd be something like

code:

Horror        Clearing House       Smith                    
              St. Martin's Press   Johnson
Comedy        Randome Book's       Takei
              Scholastic           Blagh

but I just want to know how to make it remotely readable, with the genre first. The publisher is the foreign key referencing the genre's primary key, but I don't really know how this helps when it comes to command-line queries.

# ? Mar 19, 2008 01:11

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

Isn't it as simple as:

code:

SELECT g.genre, p.publisher_name, o.owner_name
FROM genre AS g
   JOIN publisher AS p USING (genre_id)
   JOIN owner AS o ON (o.publisher_id = p.publisher_id)
ORDER BY g.genre, p.publisher_name, o.owner_name

# ? Mar 19, 2008 02:42

Lancer383: Aug 15, 2007; Say what you want about the tenants of national socialism, Dude, at least it was an ethos.

Whilst farting I posted:

This is relating to a book database, I have no idea how to word this query.

For each genre, list the genre, all of the publishers of that genre, and owner of each publisher. The problem: each of these are in different tables and I don't know how to format this without it looking like total poo poo.

Ideally, it'd be something like
code:
Horror        Clearing House       Smith                    
              St. Martin's Press   Johnson
Comedy        Randome Book's       Takei
              Scholastic           Blagh
but I just want to know how to make it remotely readable, with the genre first. The publisher is the foreign key referencing the genre's primary key, but I don't really know how this helps when it comes to command-line queries.

Minato's example will get you there, except it will look like:

code:

Horror        Clearing House       Smith                    
Horror        St. Martin's Press   Johnson
Comedy        Randome Book's       Takei
Comedy        Scholastic           Blagh

A database isn't really where you should be working on the presentation of your data -- you need to end up doing the presentation with wherever you're looking to display the data.

# ? Mar 19, 2008 04:24

Puddleboy: Feb 15, 2008

Sorry, misposted!

Puddleboy fucked around with this message at 19:10 on Mar 19, 2008

# ? Mar 19, 2008 19:06

Lt Moose: Aug 8, 2007; moose

Lt Moose fucked around with this message at 05:38 on Apr 4, 2016

# ? Mar 20, 2008 00:42

Whilst farting I: Apr 25, 2006

minato posted:

Isn't it as simple as:

code:

SELECT g.genre, p.publisher_name, o.owner_name
FROM genre AS g
   JOIN publisher AS p USING (genre_id)
   JOIN owner AS o ON (o.publisher_id = p.publisher_id)
ORDER BY g.genre, p.publisher_name, o.owner_name

You're right, it is. The "order by" part escaped me. Thank you!

Lancer383 posted:

Minato's example will get you there, except it will look like:
code:
Horror        Clearing House       Smith                    
Horror        St. Martin's Press   Johnson
Comedy        Randome Book's       Takei
Comedy        Scholastic           Blagh
A database isn't really where you should be working on the presentation of your data -- you need to end up doing the presentation with wherever you're looking to display the data.

Yeah, it did seem like a bit of an odd request. :smith:

Is there any way to display comments when running sql in batch mode? // is executed literally, # is ignored, as is /*...*/. The --comments command was introduced in MySQL 5.1.23 and the network is running 5.0.24.

Whilst farting I fucked around with this message at 00:56 on Mar 20, 2008

# ? Mar 20, 2008 00:49

Stephen: Feb 6, 2004; Stoned

I'm trying to select in MySQL one row for each auto in my Photos table where the orderid column is the lowest value:

code:

SELECT id, auto_id
FROM photos 
GROUP BY auto_id
ORDER BY orderid ASC

This returns one row per auto, however it always returns the id of the last row instead of the id of the row with the lowest orderid.

Can anyone give me a hint on how I can get around this?

edit: Nevermind it seems I found a way around it by:

code:

SELECT * FROM (
SELECT id, auto_id
FROM photos 
ORDER BY orderid ASC 
) as test GROUP BY auto_id

I don't know how efficient this is, but oh well, it works.

Stephen fucked around with this message at 16:35 on Mar 20, 2008

# ? Mar 20, 2008 16:28

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

Stephen posted:

I'm trying to select in MySQL one row for each auto in my Photos table where the orderid column is the lowest value:
code:
SELECT id, auto_id
FROM photos 
GROUP BY auto_id
ORDER BY orderid ASC 
This returns one row per auto, however it always returns the id of the last row instead of the id of the row with the lowest orderid.

Can anyone give me a hint on how I can get around this?

edit: Nevermind it seems I found a way around it by:
code:
SELECT * FROM (
SELECT id, auto_id
FROM photos 
ORDER BY orderid ASC 
) as test GROUP BY auto_id
I don't know how efficient this is, but oh well, it works.

It may work, but it probably shouldn't, and it definitely won't work anywhere but MySQL. In T-SQL or Oracle you could use windowing functions. In MySQL a better solution might be

code:

SELECT p1.id, p1.auto_id
FROM photos p1 left outer join
     photos p2 on p1.auto_id = p2.auto_id and p1.orderid < p2.orderid
where p2.id is null

# ? Mar 20, 2008 16:43

Stephen: Feb 6, 2004; Stoned

Yeah, I figured I could do it with a join, but I was hoping there was a simple obvious answer that I was too stupid to see.

# ? Mar 20, 2008 16:58

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

Stephen posted:

Yeah, I figured I could do it with a join, but I was hoping there was a simple obvious answer that I was too stupid to see.

Not with MySQL, I don't believe.

# ? Mar 20, 2008 17:53

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

In MySQL let's say you have two tables, users and articles, and articles have a column keeping track of the number of hits. I want to get a list of users and the total number of hits that articles they own have gotten, ordered by number of hits descending.

Something like:

SELECT user.username, SUM(article.hits) AS totalHits FROM user JOIN article ON article.owner = user.id GROUP BY article.id ORDER BY totalHits DESC LIMIT 20

What column am I supposed to be grouping by? article.id seems to work alright, but I don't really understand why. This query is also very slow, is there any way to speed it up aside from keeping a totalHits count in my user table?

# ? Mar 20, 2008 21:22

Puddleboy: Feb 15, 2008

fletcher posted:

In MySQL let's say you have two tables, users and articles, and articles have a column keeping track of the number of hits. I want to get a list of users and the total number of hits that articles they own have gotten, ordered by number of hits descending.

Something like:

SELECT user.username, SUM(article.hits) AS totalHits FROM user JOIN article ON article.owner = user.id GROUP BY article.id ORDER BY totalHits DESC LIMIT 20

What column am I supposed to be grouping by? article.id seems to work alright, but I don't really understand why. This query is also very slow, is there any way to speed it up aside from keeping a totalHits count in my user table?

So for each user, there are multiple articles?

You want to know how many hits all of their articles together have gotten? It looks like your query will only work if each user only has one article.

I think you want to group by the article owner, summing the total hits across multiple articles for each owner:

code:

SELECT   user.username, SUM(article.hits) as totalHits
FROM user
JOIN article ON article.owner=user.id
GROUP BY article.owner
ORDER BY totalHits DESC
LIMIT 20;

# ? Mar 20, 2008 21:29

other people: Jun 27, 2004; Associate Christ

If I want to store a users prefrence, and it is just going to be an on or off value, should I use a tiny int field type? Or is there a better way?

# ? Mar 20, 2008 22:07

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

I'm pretty sure there's a boolean type for column values.

However, I use a tinyint(1) for flags like that, just in case a third option creeps up at a later date.

# ? Mar 20, 2008 22:12

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Puddleboy posted:

So for each user, there are multiple articles?

You want to know how many hits all of their articles together have gotten? It looks like your query will only work if each user only has one article.

I think you want to group by the article owner, summing the total hits across multiple articles for each owner:
code:
SELECT   user.username, SUM(article.hits) as totalHits
FROM user
JOIN article ON article.owner=user.id
GROUP BY article.owner
ORDER BY totalHits DESC
LIMIT 20;

Ah, that is what I needed. So what about the performance issue? It takes ~4s to run this query.

# ? Mar 21, 2008 02:38

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

fletcher posted:

Ah, that is what I needed. So what about the performance issue? It takes ~4s to run this query.

Make sure you have an index on both article.owner and user.id.

# ? Mar 21, 2008 06:46

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

minato posted:

Make sure you have an index on both article.owner and user.id.

user.id is the primary key for the user table

For the article table I have article.id as the primary.

There are also separate indexes for article.owner, article.pictureId, and article.hits.

When I do an EXPLAIN for the slow query, I see this:

possible_keys: owner
key: null
ref: null
rows: 315615 (which is the size of that table)
Extra: Using where; Using temporary; Using filesort

edit: Side question, but how do I chose my CHARSET and COLLATE for a table?

fletcher fucked around with this message at 07:47 on Mar 21, 2008

# ? Mar 21, 2008 07:18

nbv4: Aug 21, 2002; by Duchess Gummybuns

How can I sort a table like this:

code:

item_id    |   value1     |  value2
1                36
2                26
3                              69
4                12
5                              9
6                78
7                              436
8                2
9                2

So it returns like this:

code:

item_id    |   value1     |  value2
7                              436
6                78
3                              69
1                36
2                26
4                12
5                              9
8                2
9                2

See how I did that? The sorting is based on two different columns at the same time. An easy solution would be to make a third column that is just the two columns added together, but I suck at SQL so hard I can't even figure out how to do that :shobon:

nbv4 fucked around with this message at 06:37 on Mar 24, 2008

# ? Mar 22, 2008 13:05

Boogeyman: Sep 29, 2004; Boo, motherfucker.

nbv4 posted:

See how I did that? The sorting is based on two different columns at the same time. An easy solution would be to make a third column that is just the two columns added together, but I suck at SQL so hard I can't even figure out how to do that

If you're using MSSQL, this will work (just tested it).

code:

SELECT
  item_id,
  value1,
  value2
FROM
  whatever
ORDER BY
  COALESCE(value1, value2) DESC

COALESCE returns the first non-null argument that was passed to it. If either value1 or value2 is null for each row, this will work.

Boogeyman fucked around with this message at 19:12 on Mar 24, 2008

# ? Mar 24, 2008 18:56

nbv4: Aug 21, 2002; by Duchess Gummybuns

Boogeyman posted:

If you're using MSSQL, this will work (just tested it).
code:
SELECT
  item_id,
  value1,
  value2
FROM
  whatever
ORDER BY
  COALESCE(value1, value2) DESC
COALESCE returns the first non-null argument that was passed to it. If either value1 or value2 is null for each row, this will work.

That actually doesn't work because value1 is actually "SUM(another_value) AS value1" so it gives me this error: "#1247 - Reference 'value1' not supported (reference to group function)"

I think making a third column would be best because some instances there may be a value in both value1 and value2.

# ? Mar 24, 2008 23:03

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Anybody have any ideas about my indexing/performance issue a few posts up? Or about CHARSET and COLLATE?

# ? Mar 25, 2008 19:09

Alex007: Jul 8, 2004

nbv4 posted:

That actually doesn't work because value1 is actually "SUM(another_value) AS value1" so it gives me this error: "#1247 - Reference 'value1' not supported (reference to group function)"

I think making a third column would be best because some instances there may be a value in both value1 and value2.

You can't sort on grouped fileds, but you can do it (sort) in an outer select:

code:


SELECT * FROM (SELECT SUM(field1) AS f1, SUM(field2) AS f2 FROM mytable) AS mydata ORDER BY f1+f2

Alex007 fucked around with this message at 19:48 on Mar 25, 2008

# ? Mar 25, 2008 19:37

Boogeyman: Sep 29, 2004; Boo, motherfucker.

I have a generic question about indexes in SQL Server 2005. Let's say I have the following tables (PK = primary key, FK = foreign key)...

Customer
-CustomerId (PK)
-CustomerName

Account
-AccountId (PK)
-CustomerId (FK)
-AccountNumber

Device
-DeviceId (PK)
-AccountId (FK)
-DeviceName
-PhoneNumber

MSSQL automatically creates indexes on primary keys, so after creating these tables, I already have an index on Customer.CustomerId, Account.AccountId, and Device.DeviceId.

Now, common sense says that I should have indexes on the foreign keys to boost MSSQL join performance. So, I add indexes on Account.CustomerId and Device.AccountId.

Finally, I want to search on a couple of fields in my application, so I add indexes for Customer.CustomerName, Account.AccountNumber, and Device.PhoneNumber.

So, for indexes, I have individual indexes for the following tables/columns...

Customer.CustomerId
Account.AccountId
Device.DeviceId
Account.CustomerId
Device.AccountId
Customer.CustomerName
Account.AccountNumber
Device.PhoneNumber

Here's the question. Am I OK leaving all of these indexes as single column indexes, or would I be better off combining some of them? For instance, instead of having separate indexes for Account.AccountId and Account.CustomerId, or should I include both columns in the primary key index that was created with the table? Likewise for Device.DeviceId and Device.AccountId.

I'm just wondering if MSSQL is smart enough to use the single column indexes efficiently, or if grouping the columns together would help it out. I know that there's no 100% correct answer and that I need to test all of this on my data set, but I'm just trying to find out if there's a general guideline for stuff like this.

# ? Mar 25, 2008 23:29

functional: Feb 12, 2008

DISTINCT keyword

Gents,

I've been looking for a walkthru on the MySQL DISTINCT keyword and haven't found anything useful.

I have a table that looks like this:

user, vote, time_entered, ipaddress, blah1, blah2, blah3

User is a VARCHAR, vote is an INT.

A user may vote many times, but I only want to take one vote from ~~the~~ each user (the biggest? the smallest? the first? the most recent?) and sum all of them up and take the average. I also want to know how many votes I just summed up.

no votes in the average may share an IP address, no votes in the average may share a username

I am using PHP to interact with MySQL

Thanks!!

functional fucked around with this message at 00:28 on Mar 26, 2008

# ? Mar 26, 2008 00:18

nbv4: Aug 21, 2002; by Duchess Gummybuns

Alex007 posted:

You can't sort on grouped fileds, but you can do it (sort) in an outer select:
code:
SELECT * FROM (SELECT SUM(field1) AS f1, SUM(field2) AS f2 FROM mytable) AS mydata ORDER BY f1+f2

hmm, thats not working either. Here is my exact query:

code:

SELECT *
FROM (
   SELECT planes.tail_number, SUM(total) AS total, SUM(flights.simulator) AS simulator
   FROM planes, flights
   WHERE planes.pilot_id = 21 AND planes.plane_id = flights.plane_id AND planes.tail_number != ""
   GROUP BY planes.tail_number) AS mytable
ORDER BY (mytable.total + mytable.simulator)

The inner SELECT works perfectly, minus the ordering. Here is an example of my output with the query above:

code:

tail_number    total    simulator
N1600U  	7.5  	NULL
N378AM  	21.5 	NULL
N53797  	1.3 	NULL
N757NJ  	39.6 	NULL
FRASCA142 	NULL 	31.3
N366AM  	27.8 	NULL
N48581  	36.0 	NULL
N69254          45.3  	NULL

as you can see, it seems to think by "ORDER BY" I really meant "RANDOMIZE BY" :rolleyes:

If I order it by either total or simulator alone, it works as expected, but adding that plus sign screwes it all up.

nbv4 fucked around with this message at 00:40 on Mar 26, 2008

# ? Mar 26, 2008 00:26

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

functional posted:

DISTINCT keyword

It sounds like what you want can be achieved with a group by statement.

code:

SELECT count(*), avg(t.vote) from (SELECT avg(vote) as vote FROM table GROUP BY user, ip_address) as t

That's kinda rough, but it should pull the average vote from a selection of each users average vote and the number of votes.

noonches fucked around with this message at 00:39 on Mar 26, 2008

# ? Mar 26, 2008 00:31

Alex007: Jul 8, 2004

nbv4 posted:

as you can see, it seems to think by "ORDER BY" I really meant "RANDOMIZE BY" If I order it by either total or simulator alone, it works as expected, but adding that plus sign screwes it all up.

In the outer SELECT, convert NULLs to zeros, because NULL + anything = NULL.

code:

ORDER BY (COALESCE(mytable.total, 0) + COALESCE(mytable.simulator,0))

# ? Mar 26, 2008 00:54

functional: Feb 12, 2008

noonches posted:

code:

SELECT count(*), avg(t.vote) from (SELECT avg(vote) as vote FROM table GROUP BY user, ip_address) as t

OK, what if I want each person's most recent vote?

I don't think I can just add ORDER BY timed_added DESC LIMIT 1 onto the end of that query you've given me. That would just sort the already DISTINCT items...?

# ? Mar 26, 2008 00:58

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

functional posted:

OK, what if I want each person's most recent vote?

I don't think I can just add ORDER BY timed_added DESC LIMIT 1 onto the end of that query you've given me. That would just sort the already DISTINCT items...?

That wouldn't work, and I think what you need would make the query a bit more complicated, maybe not tho, I could be forgetting something simple. I can't put it together in my head at the moment.

Here's a question, why would people be allowed to have more than one vote? Especially if you're not going to use them all like in this example.

# ? Mar 26, 2008 01:07

var1ety: Jul 26, 2004

nbv4 posted:

hmm, thats not working either. Here is my exact query:

Just compute your sorting column in the inner query and sort on it in the outer select directly.

# ? Mar 26, 2008 02:59

functional: Feb 12, 2008

noonches posted:

Here's a question, why would people be allowed to have more than one vote? Especially if you're not going to use them all like in this example.

The question you ask is complicated.

The answer is that, assuming we can get the query to work, this is a simpler and better solution with fewer moving parts.

We get the benefit of better logs, we give users the ability to change their votes, we don't have to code in (or maintain) restrictions on multiple voting, we don't have to assume our database is constantly in a secure state (we don't have to trust our restrictions), and we run fewer queries.

You can also make a comparison to sanitizing input for XSS before it hits the database (bad) and right before it hits the output page (good).

functional fucked around with this message at 03:10 on Mar 26, 2008

# ? Mar 26, 2008 03:02

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

I'm hesitant to post this, because I am almost positive there's a better way, and this is gonna get me scolded in here...

but assuming you have an auto-incrementing field for the votes table, this should work.

code:

SELECT count(*), avg(t.vote) 
   FROM 
     (SELECT avg(vote) AS vote 
        FROM table WHERE id IN
          (SELECT max(id) from table 
              GROUP BY user, ip_address) 
      GROUP BY user, ip_address) as t

# ? Mar 26, 2008 03:20

functional: Feb 12, 2008

noonches posted:

code:

SELECT count(*), avg(t.vote) 
   FROM 
     (SELECT avg(vote) AS vote 
        FROM table WHERE id IN
          (SELECT max(id) from table 
              GROUP BY user, ip_address) 
      GROUP BY user, ip_address) as t

I've been playing with this... When we GROUP BY on the two fields, we don't actually get what we're trying to do. It goes in the other direction: now the same user can vote twice if he comes from a different IP. And the same IP can vote twice if he comes from a different username (definitely not what we want).

Why doesn't GROUP BY have aggregate functions like LAST() or FIRST()? This is ridiculous.

This blog really hits the nail on the head:

http://www.mysqlperformanceblog.com/2007/08/17/should-mysql-extend-group-by-syntax/

functional fucked around with this message at 17:44 on Mar 26, 2008

# ? Mar 26, 2008 17:39

npe: Oct 15, 2004

functional posted:

I've been playing with this... When we GROUP BY on the two fields, we don't actually get what we're trying to do. It goes in the other direction: now the same user can vote twice if he comes from a different IP. And the same IP can vote twice if he comes from a different username (definitely not what we want).

Er, maybe I'm misunderstanding, but this doesn't make a lot of sense. If you have

code:

 User | IP
----------------
 Joe  | 1.2.3.4
 Sam  | 1.2.3.4

...you're saying you don't want these to count as two votes? In that case, where does Sam's vote go?

It sounds like you're trying to apply a constraint to your data model after the data already exists, the constraint being that users:ip's have a 1:1 ratio for votes. Is that correct?

Also, that blog is terrible because he advocates further extending MySQL's already dangerously broken group by syntax to be even more broken.

# ? Mar 26, 2008 18:08

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 20:03

functional: Feb 12, 2008

yaoi prophet posted:

Er, maybe I'm misunderstanding, but this doesn't make a lot of sense. If you have
code:
 User | IP
----------------
 Joe  | 1.2.3.4
 Sam  | 1.2.3.4
...you're saying you don't want these to count as two votes? In that case, where does Sam's vote go?

Yes. It should be ignored.

yaoi prophet posted:

It sounds like you're trying to apply a constraint to your data model after the data already exists, the constraint being that users:ip's have a 1:1 ratio for votes. Is that correct?

In the set of counted votes, no two usernames should match up, and no two IPs should match up.

# ? Mar 26, 2008 18:44

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

«‹›303 »