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 »

Victor: Jun 18, 2004

code:

with Monthly as (
    select  ds.store_id,
            month = month(ds.sale_dt),
            sale = sum(mer.unit_price_amt * ds.sale_qt)
    from    daily_sales ds
    left join merchandise mer on ds.ean_no = mer.ean_no
    where   year(ds.sale_dt) = 1998
), Numbered as (
    select  row_number = row_number() over (order by sale desc),
            *
    from    Monthly
)
select  n.month                 "Month",
        n.store_id              "Store Number",
        st.store_nm             "Store Name",
        sta.state_nm            "State",
        cast(n.sale as money)   "Total Sales Dollars"
from    Numbered n
left join store st on st.store_id = n.store_id
left join state sta on sta.state_cd = st.state_cd
where   row_number <= 10
order by ts.month

# ? Feb 15, 2008 16:31

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 22:04

GroceryBagHead: Nov 28, 2000

Hey. A very stupid question

let's say I need to construct a query that goes something like this.

1. There are many grassy knolls
2. Each knoll can have a bunch of unicorns or no unicorns at all

Problem.

I need to select all grassy knolls where there are no dead unicorns.

# ? Feb 18, 2008 22:00

Alex007: Jul 8, 2004

GroceryBagHead posted:

Hey. A very stupid question

let's say I need to construct a query that goes something like this.

1. There are many grassy knolls
2. Each knoll can have a bunch of unicorns or no unicorns at all

Problem.

I need to select all grassy knolls where there are no dead unicorns.

It should look like this:

code:

SELECT
  GrassyKnolls.*
FROM
  GrassyKnolls
  LEFT JOIN (
    --Subselect to count the dead motherfuckers per grassy knoll
    SELECT
      Unicorns.GrassyKnollId
      COUNT(*) AS DeadCount
    FROM
      Unicorns
    WHERE
      Unicorns.Dead = 1  
    GROUP BY
      Unicorns.GrassyKnollId
  ) AS DeadUnicorns
  ON GrassyKnolls.GrassyKnollId = DeadUnicorns.GrassyKnollId
WHERE
  DeadUnicorns.DeadCount IS NULL
  OR DeadUnicorns.DeadCount = 0

VVVV You were right about the OR, thanks !

Alex007 fucked around with this message at 22:24 on Feb 18, 2008

# ? Feb 18, 2008 22:14

IsaacNewton: Jun 18, 2005

Alex007 posted:

It should look like this:

code:

  DeadUnicorns.DeadCount IS NULL
  AND DeadUnicorns.DeadCount = 0

I believe the AND should be an OR.

I'd do something like this:

code:

SELECT
  GrassyKnolls.*
FROM
  GrassyKnolls
WHERE
  GrassyKnolls.id NOT IN (SELECT
      Unicorns.GrassyKnollId
    FROM
      Unicorns
    WHERE
      Unicorns.Dead = 1  
    GROUP BY
      Unicorns.GrassyKnollId)

# ? Feb 18, 2008 22:17

GroceryBagHead: Nov 28, 2000

Thanks Alex, you're the best. I caught the OR thing as well

# ? Feb 18, 2008 22:37

Alex007: Jul 8, 2004

GroceryBagHead posted:

Thanks Alex, you're the best. I caught the OR thing as well

Hey, no problem

IsaacNewton's solution is great too, depending on the number or grassy knolls and/or dead unicorns, his solution may be faster (if there are fewer grassy knoll with dead unicorns in them).

# ? Feb 18, 2008 22:47

Alex007: Jul 8, 2004

Even faster now, with a LEFT anti-JOIN the exclude knolls with dead unicorns in them:

code:

SELECT
  GrassyKnolls.*
FROM
  GrassyKnolls
  LEFT JOIN (
    --Subselect to list ONLY the knolls with dead motherfuckers in them
    SELECT
      Unicorns.GrassyKnollId
    FROM
      Unicorns
    WHERE
      Unicorns.Dead = 1  
    GROUP BY
      Unicorns.GrassyKnollId
    HAVING
      COUNT(*) > 0
  ) AS DeadUnicorns
  ON GrassyKnolls.GrassyKnollId = DeadUnicorns.GrassyKnollId
WHERE
  DeadUnicorns.GrassyKnollId IS NULL

# ? Feb 18, 2008 22:50

Fart Shark: Jun 17, 2001; AArgh!
AAAAhh!

Victor posted:

code:

with Monthly as (
    select  ds.store_id,
            month = month(ds.sale_dt),
            sale = sum(mer.unit_price_amt * ds.sale_qt)
    from    daily_sales ds
    left join merchandise mer on ds.ean_no = mer.ean_no
    where   year(ds.sale_dt) = 1998
), Numbered as (
    select  row_number = row_number() over (order by sale desc),
            *
    from    Monthly
)
select  n.month                 "Month",
        n.store_id              "Store Number",
        st.store_nm             "Store Name",
        sta.state_nm            "State",
        cast(n.sale as money)   "Total Sales Dollars"
from    Numbered n
left join store st on st.store_id = n.store_id
left join state sta on sta.state_cd = st.state_cd
where   row_number <= 10
order by ts.month

I'm sorry, I don't think I was clear enough. I need to return the top store of each month. Here is an example output:

code:

Month    |Store     |Sales
January  |Store04   |9999
February |Store09   |89888
March    |...
...

# ? Feb 19, 2008 16:45

Victor: Jun 18, 2004

code:

with Monthly as (
    select  ds.store_id,
            month = month(ds.sale_dt),
            sale = sum(mer.unit_price_amt * ds.sale_qt)
    from    daily_sales ds
    left join merchandise mer on ds.ean_no = mer.ean_no
    where   year(ds.sale_dt) = 1998
), Numbered as (
    select  row_number = row_number() over (partition by month order by sale desc),
            *
    from    Monthly
)
select  n.month                 "Month",
        n.store_id              "Store Number",
        st.store_nm             "Store Name",
        sta.state_nm            "State",
        cast(n.sale as money)   "Total Sales Dollars"
from    Numbered n
left join store st on st.store_id = n.store_id
left join state sta on sta.state_cd = st.state_cd
where   row_number = 1
order by ts.month

(Yes, I did change it!)

# ? Feb 19, 2008 16:49

Victor: Jun 18, 2004

GroceryBagHead posted:

1. There are many grassy knolls
2. Each knoll can have a bunch of unicorns or no unicorns at all

Problem.

I need to select all grassy knolls where there are no dead unicorns.

code:

select  g.*
from    GrassyKnolls g
left join Unicorns u on u.Dead = 1 and u.GrassyKnollId = g.id
where   u.id is null

# ? Feb 19, 2008 16:50

Alex007: Jul 8, 2004

Victor posted:

code:

select  g.*
from    GrassyKnolls g
left join Unicorns u on u.Dead = 1 and u.GrassyKnollId = g.id
where   u.id is null

You're gonna have to throw a DISTINCT there, otherwise hello grassy knolls multiplication !

# ? Feb 19, 2008 16:54

Victor: Jun 18, 2004

I don't know how to say this, other than no. Anti-joins like that don't need DISTINCT.

# ? Feb 19, 2008 16:55

Alex007: Jul 8, 2004

Victor posted:

I don't know how to say this, other than no. Anti-joins like that don't need DISTINCT.

You're right, I'm a dumbass for not thinking about it, sorry.

# ? Feb 19, 2008 18:24

Victor: Jun 18, 2004

That's actually not surprising -- I have seen very few people add a condition to a left join statement that has nothing to do with the join. It's a sad state of affairs, that SQL is so poorly taught... :saddowns:

# ? Feb 19, 2008 18:39

Xae: Jan 19, 2005

Victor posted:

That's actually not surprising -- I have seen very few people add a condition to a left join statement that has nothing to do with the join. It's a sad state of affairs, that SQL is so poorly taught...

Sad, but profitable. You can spend a couple weeks, maybe a month, learning about SQL and how to properly use it, and every one thinks you are some super genius.

# ? Feb 20, 2008 02:20

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

Xae posted:

Sad, but profitable. You can spend a couple weeks, maybe a month, learning about SQL and how to properly use it, and every one thinks you are some super genius.

In the next couple weeks I'm totally gonna blow everyone at my job away when I break out the MDX.

# ? Feb 20, 2008 03:09

Stephen: Feb 6, 2004; Stoned

Here's an easy query that I just can't figure out:

code:

SELECT 
	blogs.*
	blogListings.blogPostingSubmit as bps, 
	DATE_FORMAT(
		blogListings.blogPostingSubmit, '%M %e, %Y'
	) AS blogPostingSubmit2, 
FROM blogs
INNER JOIN blogListings ON blogListings.blogid = blogs.id 
WHERE 
	blogListings.blogPostingApproved=1 AND 
	blogs.blogActive=1 
GROUP BY blogs.id 
ORDER BY bps DESC

What I'm hoping to do with this query is return the most recent Blog entry by date in the BPS field, however out of the three possible entries, it's posting the second to last one from January instead of the last one from February. Can anyone tell me what I'm doing wrong?

# ? Feb 21, 2008 17:30

Victor: Jun 18, 2004

You're doing a GROUP BY and then selecting columns that aren't grouped by, without aggregating. That doesn't make sense. MySQL lets you do it because sometimes it works, and you're supposed to know when it won't, even though it might look like you're getting good data. Your code below is playing Implementation Roulette.

This post gives a template for what you want to do: for every set of records, you want to choose the min/max by some criteria. You make that its own derived table, pull the min/max value(s) out, as well as whatever designs "set of records", and then join that back to the main table and get everything you need. Perhaps I should put this idea in tsunami...

# ? Feb 21, 2008 17:49

Stephen: Feb 6, 2004; Stoned

Thanks again, Victor. I guess I've always just misused GROUP BY as well to try and eliminate rows based on my WHERE clauses.

# ? Feb 21, 2008 18:04

Victor: Jun 18, 2004

If you need more help, do let me know -- I was just being lazy and/or giving you more opportunity to learn on your own. (If I help, you'll probably learn less, but it will definitely take less time, so it's a trade-off.)

I'm beginning to think that SQL might be as misunderstood as Javascript.

# ? Feb 21, 2008 18:44

Stephen: Feb 6, 2004; Stoned

No, that was perfect thanks. I'm less of a hand-out and more of a hand-up person as well.

# ? Feb 21, 2008 20:06

mister_gosh: May 24, 2002

I want to get a result set through a recursively selecting query.

Table: relationships

code:

parent child
123    124

parent child
124    125
124    126

parent child
125    127
125    128
125    129

parent child
126    130
126    131

parent child
127    188

So, what I would want to end up with is 124,125,126,127,128,129,130,131,188 in my result set.

Put simply, I want all of the descendents of 123 according to this table (I should note that by descendents, I do mean that loosely, I don't think there is anything within the sql table linking them together other than the parent and child names).

At the time of execution, the only name/number I know is 123. Is there a way to do this?

Thanks for any input in advance!

# ? Feb 21, 2008 21:26

var1ety: Jul 26, 2004

Oracle has a CONNECT BY operator that lets you do this.

quote:

select c
from foo
connect by (foo.p = prior foo.c)
start with foo.p = 123
order by 1

quote:

124
125
126
127
128
129
130
131
188

If I couldn't do that I would write a recursive row-returning function to crawl the tree.

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

The above link has info on MySQL approaches to handling nested data.

# ? Feb 21, 2008 21:39

mister_gosh: May 24, 2002

var1ety posted:

Oracle has a CONNECT BY operator that lets you do this.

If I couldn't do that I would write a recursive row-returning function to crawl the tree.

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

The above link has info on MySQL approaches to handling nested data.

This works perfectly. Thanks!!

# ? Feb 21, 2008 21:47

Victor: Jun 18, 2004

var1ety posted:

Oracle has a CONNECT BY operator that lets you do this.

Color me jealous. SQL 2005 has Recursive CTEs, but the CONNECT BY syntax is just sexy for the simple case.

# ? Feb 21, 2008 22:02

Xae: Jan 19, 2005

Anyone have experience with Unit Testing frameworks for (PL)SQL? I have looked at UTPLSQL and SQLUnit, but I figure there has to be more than just two out there. Both of them look to be overly complex for just PL/SQL testing, as they both seem to be hacks to get JUnit to test SQL.

# ? Feb 24, 2008 05:53

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

PHP/PDO question: If I have a table with an auto-incrementing id field, what is the fastest way to get the id of a new row I just throw in? Just do a SELECT? Or is there some shortcut?

edit: Another question - This query only returns articles that have comments, how do I get it to return the ones that don't have any comments? Looking at the joins tutorial it looks like LEFT OUTER JOIN is what I want, but the query returns the same thing when I try it.

code:

SELECT 
	COUNT(comment.id) AS numComments, 
	article.id, 
	FROM article 
		JOIN comment ON article.id = comment.articleId 
	GROUP BY comment.articleId

fletcher fucked around with this message at 01:29 on Feb 27, 2008

# ? Feb 26, 2008 22:05

Xae: Jan 19, 2005

fletcher posted:

PHP/PDO question: If I have a table with an auto-incrementing id field, what is the fastest way to get the id of a new row I just throw in? Just do a SELECT? Or is there some shortcut?

edit: Another question - This query only returns articles that have comments, how do I get it to return the ones that don't have any comments? Looking at the joins tutorial it looks like LEFT OUTER JOIN is what I want, but the query returns the same thing when I try it.
code:
SELECT 
	COUNT(comment.id) AS numComments, 
	article.id, 
	FROM article 
		JOIN comment ON article.id = comment.articleId 
	GROUP BY comment.articleId

Edit: Try specifying "LEFT" or "RIGHT" join, instead of just "JOIN", see if that helps.

Ghetto Method:

code:

    SELECT  article.id
    FROM    article
    WHERE   article.id NOT IN (SELECT comment.articleID FROM comment)

For the first question: What RDBMS are you using?

Xae fucked around with this message at 02:02 on Feb 27, 2008

# ? Feb 27, 2008 01:59

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

Xae posted:

Ghetto Method:
code:
    SELECT  article.id
    FROM    article
    WHERE   article.id NOT IN (SELECT comment.articleID FROM comment)
For the first question: What RDBMS are you using?

I'm running MySQL 5.

Whoops, I phrased my question a little wrong. I want it to return articles with comments AND articles without comments in the same query, but count the # of comments if it does have any.

edit: specifying LEFT or RIGHT join gives me the same results

# ? Feb 27, 2008 02:03

brae: Feb 2, 2006

fletcher posted:

PHP/PDO question: If I have a table with an auto-incrementing id field, what is the fastest way to get the id of a new row I just throw in? Just do a SELECT? Or is there some shortcut?

http://www.php.net/manual/en/function.PDO-lastInsertId.php

Or SELECT LAST_INSERT_ID() if you want to do it in SQL (you said you were using MySQL 5).

EDIT: Does this do what you want? It's a lot like your query above, but using an outer join, which you were right to think you needed to use.

code:

mysql> select * from articles;
+----+
| id |
+----+
|  1 | 
|  2 | 
|  3 | 
+----+


mysql> select * from comments;
+----+------------+
| id | article_id |
+----+------------+
|  1 |          1 | 
|  2 |          1 | 
|  3 |          1 | 
|  4 |          2 | 
+----+------------+


mysql> select a.id as article_id, count(c.id) as comment_count from 
                articles a left outer join comments c on a.id=c.article_id group by a.id;
+------------+---------------+
| article_id | comment_count |
+------------+---------------+
|          3 |             0 | 
|          1 |             3 | 
|          2 |             1 | 
+------------+---------------+

brae fucked around with this message at 02:29 on Feb 27, 2008

# ? Feb 27, 2008 02:17

Xae: Jan 19, 2005

fletcher posted:

I'm running MySQL 5.

Whoops, I phrased my question a little wrong. I want it to return articles with comments AND articles without comments in the same query, but count the # of comments if it does have any.

edit: specifying LEFT or RIGHT join gives me the same results

code:

SELECT  article.ID, COUNT(*)
FROM	article
    LEFT JOIN comment ON article.ID = comment.ArticleID
GROUP BY article.ID

That should work, I don't have any DB handy to try it, so I probably hosed up the LEFT/RIGHT or order.

If it doesn't look at your data, it may be that you don't have any, or some other issue exists.

# ? Feb 27, 2008 03:53

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

brae posted:

http://www.php.net/manual/en/function.PDO-lastInsertId.php

Or SELECT LAST_INSERT_ID() if you want to do it in SQL (you said you were using MySQL 5).

EDIT: Does this do what you want? It's a lot like your query above, but using an outer join, which you were right to think you needed to use.
code:
<snip>

Awesome, this worked great, thanks! And thank you to everybody else who answered as well.

# ? Feb 27, 2008 09:10

Victor: Jun 18, 2004

So it's LAST_INSERT_ID() for MySQL 5 (what about 4?), SCOPE_IDENTITY() for SQL Server 2000+, what about Oracle, Postgre?

# ? Feb 27, 2008 14:48

npe: Oct 15, 2004

Victor posted:

So it's LAST_INSERT_ID() for MySQL 5 (what about 4?), SCOPE_IDENTITY() for SQL Server 2000+, what about Oracle, Postgre?

Oracle uses RETURNING:

code:


 INSERT INTO my_table (foo, bar) VALUES ('foo', 'bar') RETURNING id INTO :id

You would then retrieve the bind variable as normal.

# ? Feb 27, 2008 15:00

Senso: Nov 4, 2005; Always working

I'm using Oracle to build a query that has me scratching my head. I simply cannot find a way to do the following
in a single big query. I don't really know PL/SQL so I don't want to go that way, if possible.

So here's my example.

Let's say I have a table FOOBAR with data:

code:

foo		bar		fred
1		a		10
2		b		10
3		c		10
4		d		11
5		e		11
6		f		12
7		g		13
8		h		14
9		i		15
10		j		15

I want to have a list of all 'foo' that have a unique 'fred'. Ex. 1 does not match because its 'fred' (10) appears 3 times in that table.

Basically, I have a query like that:

code:

SELECT foo,COUNT(*) FROM foobar
WHERE fred IS NOT NULL 
GROUP BY foo HAVING COUNT(*) = 1;

Which would return:

FOO COUNT(*)
--- --------
6   1
7   1
8   1

The problem is, I want to return ONLY the 'foo' column because I need to pass the results in another 'WHERE x in (...)' query. The COUNT(*) column does not allow me to pass the results.

Any ideas? I don't know PL/SQL but maybe it would be possible to store the first results into an array or something and then loop over that...

# ? Feb 27, 2008 15:21

npe: Oct 15, 2004

You should be able to use either IN() or an inline view. Like...

code:

SELECT * FROM blah WHERE foo IN
(SELECT foo,COUNT(*) FROM foobar
 WHERE fred IS NOT NULL 
 GROUP BY foo HAVING COUNT(*) = 1)

-- alternately, with an inline view

SELECT * FROM 
 blah JOIN
 (SELECT foo,COUNT(*) FROM foobar
  WHERE fred IS NOT NULL 
  GROUP BY foo HAVING COUNT(*) = 1) s ON s.foo = blah.foo

# ? Feb 27, 2008 15:31

Senso: Nov 4, 2005; Always working

yaoi prophet posted:

code:

SELECT * FROM blah WHERE foo IN
(SELECT foo,COUNT(*) FROM foobar
 WHERE fred IS NOT NULL 
 GROUP BY foo HAVING COUNT(*) = 1)

This does not work, I get a "ORA-00913: too many values" error. I'll try to make the second option work and let you know how that goes.

# ? Feb 27, 2008 15:55

npe: Oct 15, 2004

Senso posted:

This does not work, I get a "ORA-00913: too many values" error. I'll try to make the second option work and let you know how that goes.

That's because I'm stupid. Take out the COUNT(*) from the select clause:

code:

SELECT * FROM blah WHERE foo IN
(SELECT foo FROM foobar
 WHERE fred IS NOT NULL 
 GROUP BY foo HAVING COUNT(*) = 1)

Sorry!

# ? Feb 27, 2008 16:00

Senso: Nov 4, 2005; Always working

^^^^ That works perfectly, thanks!

# ? Feb 27, 2008 16:31

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 22:04

dest: May 28, 2003; 9/9/99
Never Forget; Grimey Drawer

I need a custom Excel SQL query written for my job. The boss wants it, and I don't know a thing about SQL. If I wanted to pay a goon to write it for me, should I post it in here somewhere, or over in SA Mart?

We run a MS SQL server. I have 2 tables that I need to pull data from. One is a table with all of our vendors, the other is a table that records the payments that are made to them by date. There is a common "Vendor Code" field in both tables. The boss wants a spreadsheet that lists all our active vendors, and how much we paid to them by week.

The first report I gave to him, I just queried the payments by week, but that didn't work, because he wants all the vendors listed, whether a payment was made to them that week or not, so he can go line by line, look at all the vendors by week, and look at our buying trends.

Since the first time I looked at SQL was the day he asked me for this, and since I can't figure out how to do this with one script, what I've done is made a bunch of different scripts, and then combined the data in excel. I queried our master vendor file, had it filter by active vendors and by a certain last payment date, to filter out all the old vendors, and put it in the first column.

Then I have a script that queries the vendor payments table, and I have it filter between dates. I run it for every week and put the data in a separate excel table. I have an excel plugin called "Merge Tables Wizard" that I run on each week, and it takes the numbers, and merges them to the appropriate vendor. I have to run it for each week. That gives me something like this:

With the totals for each week at the bottom.

The problem with doing it by hand, besides being completely idiotic, is that new vendors are added all the time, so every Friday when I create the report I have to redo the main vendor list and re-merge all the data from the previous weeks, plus add the data from the current week. The query would also have to combine multiple payments to the same vendor during the same week, so that there is just the one total dollar amount.

Aaaaaaanyway, should I just post this in SA Mart, and what's the going rate for something like this? If I got a quote, I could run it by the boss, or if it was reasonable enough, just pay it myself, because doing this by hand blows.

# ? Feb 29, 2008 01:09

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

«‹›303 »