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'

«‹›6 »

Victor: Jun 18, 2004

fletcher, unless I'm blind, that code should work. Perhaps you could provide code for creating the picture and comment tables with enough sample data to demonstrate the problem?

# ¿ Dec 28, 2007 22:50

Adbot: ADBOT LOVES YOU

# ¿ May 13, 2024 01:23

Victor: Jun 18, 2004

Xae, joins will possibly increase the number of fields returned, especially if one is performing a semi-join (joining table A against B, but only selecting columns from A). In fact, incorrectly using semi-joins causes people to "fix" the results by slapping in a DISTINCT, where the proper behavior would be to change from a join to an IN subquery.

# ¿ Jan 2, 2008 13:49

Victor: Jun 18, 2004

This uses SQL2005 Common Table Expressions, but you can easily just build the two tables manually.

code:

with A as (
    select id = 1, value = 2 union
    select id = 2, value = 4
), B as (
    select id = 1, a_id = 1, value = 'A' union
    select id = 1, a_id = 1, value = 'B'
)
select  A.*
from    A
--inner join B on B.a_id = A.id
where   id in (select a_id from B)

Results:

code:

id  value
--  -----
1   2

Uncommenting inner... and commenting where...:

code:

id  value
--  -----
1   2
1   2

# ¿ Jan 3, 2008 00:48

Victor: Jun 18, 2004

Shouldn't there be some documentation on MySQL full text search on how to do this stuff? I would be shocked if this were the first time such functionality has been required.

# ¿ Jan 7, 2008 18:50

Victor: Jun 18, 2004

Clever, but I don't believe that documentation actually addresses "how to do this stuff".

# ¿ Jan 7, 2008 20:27

Victor: Jun 18, 2004

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. :v:

# ¿ Jan 10, 2008 02:49

Victor: Jun 18, 2004

cletus42o, RDBMS?

# ¿ Jan 10, 2008 17:11

Victor: Jun 18, 2004

SQL2005, with a few of my own CLR functions that should be obvious or irrelevant:

code:

with fw_league as (
    select  id = value
    from    dbo.RegexSplit('1,2', ',')
), full_fw_user as (
    select  fw_user_id = p.[0],
            fw_league_id  = p.[1]
    from    dbo.RegexSplit(dbo.RegexReplace('
        !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
        '
        , '(?m:^(\s*!.*)?\r\n)|(?-m:\s+$)|(?m:^\s+)', ''), '\r\n') lines
    cross apply dbo.RegexSplit(value, '\s+') cols
    pivot (max(cols.value) for cols.idx in ([0], [1])) p
),  fw_user_total as (
    select  fw_user_id = p.[0],
            points  = cast(p.[1] as int),
            num_correct = cast(p.[2] as int)
    from    dbo.RegexSplit(dbo.RegexReplace('
        !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
        '
        , '(?m:^(\s*!.*)?\r\n)|(?-m:\s+$)|(?m:^\s+)', ''), '\r\n') lines
    cross apply dbo.RegexSplit(value, '\s+') cols
    pivot (max(cols.value) for cols.idx in ([0], [1], [2])) p
), [b]/*** start reading here ***/[/b] numbered as (
    select  row_number = row_number() over (partition by fw_league_id order by points desc),
            points,
            fw_league_id,
            num_correct
    from    full_fw_user u
    inner join fw_user_total t on t.fw_user_id = u.fw_user_id
)
select  fw_league_id, 
        x_points = replace(dbo.JoinWithPipe(points), '|', '+'),
        sum_points = sum(points),
        x_correct = replace(dbo.JoinWithPipe(num_correct), '|', '+'),
        sum_correct = sum(num_correct)
from    numbered
where   row_number <= 5
group by fw_league_id

Results:

code:

fw_league_id   x_points         sum_points   x_correct    sum_correct
------------   --------------   ----------   ----------   -----------
1              60+50+40+35+30   215	     10+9+8+8+7   42
2              80+70+50+45+40   285	     11+8+7+8+5   39

Victor fucked around with this message at 17:22 on Jan 10, 2008

# ¿ Jan 10, 2008 17:19

Victor: Jun 18, 2004

I was lazy and didn't generate create table statements and related annoyances. See http://luke.breuer.com/time/item/SQL_2005_tables_from_strings/201.aspx .

# ¿ Jan 10, 2008 17:25

Victor: Jun 18, 2004

code:

select  fw_league_id, 
        sum_points = sum(points),
        sum_correct = sum(num_correct)
from (
    select  u.fw_user_id,
            u.fw_league_id,
            t.points,
            t.num_correct,
            count = (
                select  count(*)
                from    fw_user_total x
                inner join full_fw_user y on y.fw_user_id = x.fw_user_id
                where   y.fw_league_id = u.fw_league_id
                    and
                        (
                                x.points > t.points 
                            or  
                                x.points = t.points and x.fw_user_id < t.fw_user_id
                        )
            )
    from    full_fw_user u
    inner join fw_user_total t on t.fw_user_id = u.fw_user_id
) t
where   count < 5
group by fw_league_id

# ¿ Jan 10, 2008 22:35

Victor: Jun 18, 2004

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

# ¿ Jan 10, 2008 22:48

Victor: Jun 18, 2004

Do you have an index on `DATE`? How about `status`?

# ¿ Jan 12, 2008 11:10

Victor: Jun 18, 2004

Is this a single index, or multiple? Indexes can be on multiple columns. In particular, you want an index on (status, DATE).

# ¿ Jan 12, 2008 14:26

Victor: Jun 18, 2004

We don't know the server's specs.

# ¿ Jan 12, 2008 16:54

Victor: Jun 18, 2004

Maybe the planets aren't aligned?

# ¿ Jan 12, 2008 20:58

Victor: Jun 18, 2004

Xae posted:

Plus, if you can't be sure you data is accurate why are you bothering to store it?

I'm afraid I have a hard time seeing how you have the experience to back this up. At least, where I work, sometimes we go without constraints, but have nightly integrity checks that run to ensure there are no orphaned records. This doesn't catch the circumstance where one can have orphan records created, incorrectly, that then get deleted before this integrity check runs. However, from my small amount of experience, I do not see this special case as a bug pattern common enough to worry about.

# ¿ Jan 17, 2008 04:59

Victor: Jun 18, 2004

You're just assuming too much. The rule that programmer time is more valuable than computer cost is a good one, but it isn't all-covering, all-important, never-inapplicable. To give you an idea, checking a foreign key constraint can keep transactions open a tiny bit longer, which result in locks being held a bit longer, which can, if one is near a certain breaking point (think adding 1% to a fully packed highway), will cause the system to go crazy. Moreover, dealing with constraints can be a royal PITA, due to having to delete in the right order, and having major issues with self-referencing tables.

# ¿ Jan 17, 2008 05:35

Victor: Jun 18, 2004

Xae, one major problem with your argument is that you assume getting a new server is an easy thing to do. Combine legacy issues with politics, and it's not a simple choice. Yes, it would be nice if people were all-around competent and powerful hardware could simply be purchased. I know this because we recently got a $6K DB server and it screams. However, your making blatant generalizations are... unsettling.

# ¿ Jan 17, 2008 20:12

Victor: Jun 18, 2004

It's typically best to load the data you have into some table created to hold said data, and then do the transformation with SQL statements.

# ¿ Jan 18, 2008 21:12

Victor: Jun 18, 2004

code:

order by coalesce(manufacturer, chr(127))

Use whatever function generates a character from its code. Increase the number if you're using Unicode.

# ¿ Feb 1, 2008 06:51

Victor: Jun 18, 2004

I had "zzzzzz" written out, but then decided that was lame, breakable, and tried for something better. Oh well...

# ¿ Feb 1, 2008 21:46

Victor: Jun 18, 2004

Jethro posted:

ORDER BY CASE WHEN manufacturer IS NULL THEN 1 ELSE 0 END, manufacturer

Nice, this one is perfectly robust.

# ¿ Feb 4, 2008 07:09

Victor: Jun 18, 2004

kalleboo, where did you put the [code][/code] tags?

# ¿ Feb 5, 2008 20:09

Victor: Jun 18, 2004

Awww, I thought you were going to say that the forums were temporarily out code tags or something.

# ¿ Feb 6, 2008 00:37

Victor: Jun 18, 2004

It's blue! Prettttty...

# ¿ Feb 6, 2008 01:02

Victor: Jun 18, 2004

Walked posted:

Can someone help me understand:

http://msdn2.microsoft.com/en-us/library/ms186734.aspx

I am not getting what OVER does in this statement - I think I understand the remainder of how the paging works, but I'm somewhat new with SQL and I dont really "get" the OVER clause.

edit: Or feel free to suggest me a paging solution for SQL Server 2005 - I'm trying to avoid using the ASP.NET PagedDataSource

TIME entry on row_number()

Victor fucked around with this message at 19:40 on Feb 7, 2008

# ¿ Feb 7, 2008 19:12

Victor: Jun 18, 2004

Walked posted:

Fixed problem number 1!
But anyone know why it's erroring out on the WHERE clause?

Did you see the following comment in my code?

code:

-- the alias is required; silly syntax rules!

# ¿ Feb 8, 2008 22:41

Victor: Jun 18, 2004

If there's anything my explanation didn't cover, let me know and I'll fix it. I wrote it just for you! :unsmith:

# ¿ Feb 8, 2008 22:59

Victor: Jun 18, 2004

code:

select  h.home_Address, 
        photo_id = p.id
from    homes h
inner join (
    select  home_id,
            order_id = min(order_id)
    from    photos
    group by home_id
) min on min.home_id = h.id
inner join photos p on p.home_id = h.id and p.order_id = min.order_id

# ¿ Feb 12, 2008 23:03

Victor: Jun 18, 2004

code:

inner -> left

# ¿ Feb 12, 2008 23:17

Victor: Jun 18, 2004

With SQL2005 row_number() syntax:

code:

with P as (
    select  row_number = row_number() over (partition by home_id order by order_id)
            id,
            home_id
    from    photos
)
select  h.home_Address, 
        photo_id = p.id
from    homes h
left join P p on p.row_number = 1 and p.home_id = h.id

# ¿ Feb 12, 2008 23:39

Victor: Jun 18, 2004

SQL Server Management Studio? It's not awesome, but it has functional undo/redo, among other things.

# ¿ Feb 13, 2008 06:53

Victor: Jun 18, 2004

ray2k posted:

the only gotcha that I think some of the script generations may not be 100% compatible with sql 2000. (Not sure on that, though.)

# ¿ Feb 13, 2008 19:07

Victor: Jun 18, 2004

Fart Shark, define "top selling stores". If you need only one record, I showed how to do this above when helping Stephen. What RDBMS are you using?

# ¿ Feb 14, 2008 17:44

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

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

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

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

Adbot: ADBOT LOVES YOU

# ¿ May 13, 2024 01:23

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

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

«‹›6 »