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 »

POKEMAN SAM: Jul 8, 2004

I gots me a query question:

We're using SQL Server (2008 I think?) and my data is in a table with the following columns (simplified): PersonGuid Timestamp Item. These rows represent sales, so the table has millions of these rows, with thousands of rows per PersonGuid.

What I was to do is somehow construct a query that will give me one result set of the most recent 5 Items per PersonGuid. I have no idea how to do this other than getting a list of every PersonGuid and for each of them get the most recent 5 Items, but I figure there might be an easier way to do this.

Any ideas?

# ? Jul 16, 2010 19:22

Adbot: ADBOT LOVES YOU

# ? May 14, 2024 02:30

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Ugg boots posted:

I gots me a query question:

We're using SQL Server (2008 I think?) and my data is in a table with the following columns (simplified): PersonGuid Timestamp Item. These rows represent sales, so the table has millions of these rows, with thousands of rows per PersonGuid.

What I was to do is somehow construct a query that will give me one result set of the most recent 5 Items per PersonGuid. I have no idea how to do this other than getting a list of every PersonGuid and for each of them get the most recent 5 Items, but I figure there might be an easier way to do this.

Any ideas?

untested

code:

SELECT
  PersonGuid, Timestamp, Item
FROM
(
SELECT
  PersonGuid, Timestamp, Item, 
  RANK() OVER (PARTITION BY PersonGuid ORDER BY TimeStamp DESC) AS Rank
FROM
  what
) tmp
WHERE Rank <= 5

# ? Jul 17, 2010 00:40

mindphlux: Jan 8, 2004; by R. Guyovich

latest dumb access question :

I have a table of records of stores. each row has a UID storenumber. every week I update this information in my database with a datafeed I import. Some existing stores have data changed, and some new stores are added.

Right now, I have two select queries, one that gives me a list of relevant stores that changed, and one that gives me a list of new stores. I've cleverly used a UNION to join these two queries into one, that just gives me all stuff I need to update/append into my master table.

but of course when I use an update query to try and accomplish this, access throws up the error "Operation must use an updateable query." I don't know what the gently caress, but I'm assuming it's because I'm trying to update some records that don't exist (ie, I need to actually append the new records). I could just make seperate action queries to first update existing records, then append the new records, but why should I have to click all that stuff when there should be a way to just do them both at the same time.

I don't know a lick of VB, and I am not seeing how I'd accomplish this with a macro. Is it possible to both append and update with a single action query? google is pathetic and worthless.

# ? Jul 19, 2010 20:58

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

Can you do a REPLACE INTO query in Access? If the data you are getting has complete records, this would work.

# ? Jul 19, 2010 21:02

mindphlux: Jan 8, 2004; by R. Guyovich

sadly there's more data/columns in the store table than in the data I import, so if REPLACE INTO actually deletes records before inserting them, this wouldn't be an option...

# ? Jul 19, 2010 21:23

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

mindphlux posted:

latest dumb access question :

I have a table of records of stores. each row has a UID storenumber. every week I update this information in my database with a datafeed I import. Some existing stores have data changed, and some new stores are added.

Right now, I have two select queries, one that gives me a list of relevant stores that changed, and one that gives me a list of new stores. I've cleverly used a UNION to join these two queries into one, that just gives me all stuff I need to update/append into my master table.

but of course when I use an update query to try and accomplish this, access throws up the error "Operation must use an updateable query." I don't know what the gently caress, but I'm assuming it's because I'm trying to update some records that don't exist (ie, I need to actually append the new records). I could just make seperate action queries to first update existing records, then append the new records, but why should I have to click all that stuff when there should be a way to just do them both at the same time.

I don't know a lick of VB, and I am not seeing how I'd accomplish this with a macro. Is it possible to both append and update with a single action query? google is pathetic and worthless.

It sounds like you're updating the query, not the master table. As such, you need to use "an updateable query", and queries which have joins or unions are explicitly not updateable.

# ? Jul 19, 2010 22:00

POKEMAN SAM: Jul 8, 2004

camels posted:

untested

code:

SELECT
  PersonGuid, Timestamp, Item
FROM
(
SELECT
  PersonGuid, Timestamp, Item, 
  RANK() OVER (PARTITION BY PersonGuid ORDER BY TimeStamp DESC) AS Rank
FROM
  what
) tmp
WHERE Rank <= 5

Awesome! Works perfectly (and fast)!

# ? Jul 19, 2010 22:20

mindphlux: Jan 8, 2004; by R. Guyovich

Jethro posted:

It sounds like you're updating the query, not the master table. As such, you need to use "an updateable query", and queries which have joins or unions are explicitly not updateable.

code:

UPDATE StoreData RIGHT JOIN Changeovers 
ON StoreData.store = Changeovers.store 
SET StoreData.store = [Changeovers]![store], StoreData.entity = [Changeovers]![entity], StoreData.fein = [Changeovers]![fein];

ok, well I thought you were right and nearly just wanted to die from idiocy, but that's my SQL for the actual action query, with Changeovers being my union query.

can just like no part of an update query reference a join or a union query, or am I just being completely stupid and not getting something?

# ? Jul 19, 2010 22:50

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

mindphlux posted:

code:
UPDATE StoreData RIGHT JOIN Changeovers 
ON StoreData.store = Changeovers.store 
SET StoreData.store = [Changeovers]![store], StoreData.entity = [Changeovers]![entity], StoreData.fein = [Changeovers]![fein];
ok, well I thought you were right and nearly just wanted to die from idiocy, but that's my SQL for the actual action query, with Changeovers being my union query.

can just like no part of an update query reference a join or a union query, or am I just being completely stupid and not getting something?

I don't know Access, so bear with me.

Try changing that to an inner join? You're UPDATEing the StoreData table, so it makes no sense to do a right join.

It may be possible that no part of an update query can reference a union query, but given the way you described the union query that doesn't seem like a bad thing. Are you UNIONing the UPDATEs with the INSERTs? If so, stop it, you need two queryies anyway. One to UPDATE and one to INSERT.

# ? Jul 20, 2010 21:53

stuph: Aug 31, 2004; donkey punching my way to the top

Hammerite posted:

I think this is happening because your subquery is returning an empty set, which gets treated as NULL because it's being used in a context where MySQL expects a scalar. Now COUNT() usually returns an integer value, even if it's zero, but if you combine it with GROUP BY then it may not (if there aren't any GROUP BY values then the resultset is empty). So I think your WHERE clause is filtering out all the rows. The most likely reason for this is that VALUES is returning NULL. In the documentation, it states that "The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise". You're using it within the UPDATE clause, but inside a subquery, so I can only guess that VALUES wasn't designed to handle this situation, and it's returning NULL.

I suggest not using ON DUPLICATE KEY UPDATE here. Instead, check whether a clashing row exists before beforming the insert. To avoid a race condition, lock the table before checking and release the lock after inserting/updating as appropriate.

Optionally, throw a case statement in there (which is often a good idea anyway if you have the possibility of returning a NULL in something like this)

INSERT INTO bpamp
(brand,publication,`date`,amp)
(
SELECT brand,publication,`date`,
case when count(bpId) is null then 0 else COUNT(bpId)/100 end
FROM bp
GROUP BY brand, publication, `date`
)
ON DUPLICATE KEY UPDATE
amp =
(
SELECT case when count(bpId) is null then 0 else COUNT(bpId)/100 end
FROM bp
WHERE brand=VALUES(brand)
AND publication=VALUES(publication)
AND `date`=VALUES(`date`)
GROUP BY brand, publication, `date`);

# ? Jul 21, 2010 03:33

Bad Titty Puker: Nov 3, 2007; Soiled Meat

stuph posted:

Optionally, throw a case statement in there (which is often a good idea anyway if you have the possibility of returning a NULL in something like this)

INSERT INTO bpamp
(brand,publication,`date`,amp)
(
SELECT brand,publication,`date`,
case when count(bpId) is null then 0 else COUNT(bpId)/100 end
FROM bp
GROUP BY brand, publication, `date`
)
ON DUPLICATE KEY UPDATE
amp =
(
SELECT case when count(bpId) is null then 0 else COUNT(bpId)/100 end
FROM bp
WHERE brand=VALUES(brand)
AND publication=VALUES(publication)
AND `date`=VALUES(`date`)
GROUP BY brand, publication, `date`);

A question to the OP - Why is derived data (a simple record count) stored in a table? This looks dubious- I would look into using a view or query instead, making sure "bp" is indexed on (brand, publication, `date`).

# ? Jul 21, 2010 05:38

cynic: Jan 19, 2004

camels posted:

A question to the OP - Why is derived data (a simple record count) stored in a table? This looks dubious- I would look into using a view or query instead, making sure "bp" is indexed on (brand, publication, `date`).

The calculations are a lot more complex that I indicated in the OP, and the data is 1m+ records and expanding rapidly - I was just working with a simple example to get the basics figured out.

# ? Jul 21, 2010 11:23

Ferg: May 6, 2007; Lipstick Apathy

So I suck rear end at SQL. Why am I getting "Unknown column 'distance' in 'where clause'" when executing this:

code:

SELECT id,user_id,latitude,longitude,LatLonDistance(None,None,latitude,longitude) 
    AS distance 
    FROM users_userprofile 
    WHERE distance <= 0.5 AND personality = "Good"  
    ORDER BY distance DESC

It's a raw query in Django if that makes any difference

# ? Jul 21, 2010 20:58

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

Ferg posted:

So I suck rear end at SQL. Why am I getting "Unknown column 'distance' in 'where clause'" when executing this:
code:
SELECT id,user_id,latitude,longitude,LatLonDistance(None,None,latitude,longitude) 
    AS distance 
    FROM users_userprofile 
    WHERE distance <= 0.5 AND personality = "Good"  
    ORDER BY distance DESC
It's a raw query in Django if that makes any difference

There is no column called distance in your source table. distance is an alias for an expression in your select list.

code:

SELECT id,user_id,latitude,longitude,LatLonDistance(None,None,latitude,longitude) 
    AS distance 
    FROM users_userprofile 
    WHERE LatLonDistance(None,None,latitude,longitude) <= 0.5 AND personality = "Good"  
ORDER BY distance DESC

# ? Jul 21, 2010 21:05

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Jethro posted:

There is no column called distance in your source table. distance is an alias for an expression in your select list.
code:
SELECT id,user_id,latitude,longitude,LatLonDistance(None,None,latitude,longitude) 
    AS distance 
    FROM users_userprofile 
    WHERE LatLonDistance(None,None,latitude,longitude) <= 0.5 AND personality = "Good"  
ORDER BY distance DESC

That surprises me, I thought you were able to use aliases in a WHERE clause?

:ninja:

edit: never mind,

quote:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

# ? Jul 21, 2010 21:16

Ferg: May 6, 2007; Lipstick Apathy

Jethro posted:

There is no column called distance in your source table. distance is an alias for an expression in your select list.

Golbez posted:

That surprises me, I thought you were able to use aliases in a WHERE clause?

edit: never mind,

Thank you sirs!

# ? Jul 21, 2010 21:18

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

HAVING should also work.

code:

SELECT id,user_id,latitude,longitude,LatLonDistance(None,None,latitude,longitude) 
    AS distance 
    FROM users_userprofile 
    WHERE personality = "Good"  
    HAVING distance <= 0.5
    ORDER BY distance DESC

# ? Jul 21, 2010 21:27

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

I need help.

code:

SELECT MAX(count) as total, date_year, date_month, date_day
FROM (
    SELECT COUNT(DISTINCT user_id) AS count, date_year, date_month, date_day
    FROM logs
    WHERE action = "login" 
    AND date BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW()
    GROUP BY date_year, date_month, date_day
) AS countquery

This is to get me the day in our database with the most logins. It gives me the most logins, but the date_ fields are always the earliest in the results, in this case 2009, 7, and 21, respectively. That's not the highest day, not by a long shot.

Can I get MySQL to tell me which day had the highest number, without doing a second query of "select date_etc from logs where count(etc) = $thenumberIjustgot"? Or doing a 'order by count desc limit 1'? I'd like to think there are more elegant methods. I don't care about tiebreakers, I'll cross that bridge when I get to it.

Golbez fucked around with this message at 23:01 on Jul 21, 2010

# ? Jul 21, 2010 22:54

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

I think you just need an ORDER BY in your sub query.

code:

    SELECT COUNT(DISTINCT user_id) AS count, date_year, date_month, date_day
    FROM logs
    WHERE action = "login" 
    AND date BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW()
    GROUP BY date_year, date_month, date_day
    ORDER BY date_year DESC, date_month DESC, date_day DESC

I might be completely wrong.

# ? Jul 22, 2010 00:21

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Doctor rear end in a top hat posted:

I think you just need an ORDER BY in your sub query.

code:

    SELECT COUNT(DISTINCT user_id) AS count, date_year, date_month, date_day
    FROM logs
    WHERE action = "login" 
    AND date BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW()
    GROUP BY date_year, date_month, date_day
    ORDER BY date_year DESC, date_month DESC, date_day DESC

I might be completely wrong.

I'm not at work, but won't that order by the date, rather than the count?I mean, if I was going to resort to an order by, I'd just do it by the count and dispense with the MAX, but I was wondering if it was possible for MAX to give me the correct date.

# ? Jul 22, 2010 00:37

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

When you do an ORDER BY with a GROUP BY, the GROUP BY will keep the first record that the ORDER BY returns, plus calculate fields like counts. It should return the same thing as what you were getting, but with the newest dates. The whole query would look like this:

code:

SELECT MAX(count) as total, date_year, date_month, date_day
FROM (
    SELECT COUNT(DISTINCT user_id) AS count, date_year, date_month, date_day
    FROM logs
    WHERE action = "login" 
    AND date BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW()
    GROUP BY date_year, date_month, date_day
    ORDER BY date_year DESC, date_month DESC, date_day DESC
) AS countquery

# ? Jul 22, 2010 00:47

Question Mark Mound: Jun 14, 2006; Tokyo Crystal Mew
Dancing Godzilla

As someone with very little experience with SQL, part of my job is to code SQL queries. My employers decided this was a good idea.

Anyway, when I run this MS SQL query I get "Incorrect syntax near the keyword 'GROUP'."

code:

WITH all_picks_refs 
     AS (SELECT DISTINCT LEFT(tkt_m_reference, 6) AS 'PickRef' 
         FROM   tkt_m_movements_tbl 
         WHERE  tkt_m_container = 'PICK') 
SELECT tkt_d_part_number, 
       COUNT(*) AS 'TotalPicks' 
FROM   (SELECT tkt_d_part_number, 
               pickref, 
               COUNT(*) AS 'Qty' 
        FROM   tkt_m_movements_tbl, 
               tkt_d_ticket_def_tbl, 
               all_picks_refs 
        WHERE  LEFT(tkt_m_reference, 6) = all_picks_refs.pickref 
               AND tkt_m_d_id = tkt_d_id 
               AND tkt_m_location = 'PICK' 
        GROUP  BY pickref, 
                  tkt_d_part_number) 
GROUP  BY tkt_d_part_number

Basically, when I run the following query...

code:

WITH all_picks_refs 
     AS (SELECT DISTINCT LEFT(tkt_m_reference, 6) AS 'PickRef' 
         FROM   tkt_m_movements_tbl 
         WHERE  tkt_m_container = 'PICK') 
SELECT tkt_d_part_number, 
       pickref, 
       COUNT(*) AS 'Qty' 
FROM   tkt_m_movements_tbl, 
       tkt_d_ticket_def_tbl, 
       all_picks_refs 
WHERE  LEFT(tkt_m_reference, 6) = all_picks_refs.pickref 
       AND tkt_m_d_id = tkt_d_id 
       AND tkt_m_location = 'PICK' 
GROUP  BY pickref, 
          tkt_d_part_number

I'm getting a list of of each part number in the warehouse, a pick reference (order number, essentially) of when someone bought one, and how many of that part were bought in that order.

What I need is to be able to count how many unique picks there are per part number. For example, if I get the results from the subquery:
PartNum___PickRef___Qty 12345_____ABCDEF____1 12345_____QWERTY____3 12345_____POIUYT____2 98765_____ABCDEF____1 98765_____LKJHGF____17
I want the final query to tell me that part 12345 has 3 unique picks for it, and 98765 has 2.

I'm not entirely sure why I can't do "SELECT whatever FROM (SELECT whatever....)" in this case, since I've got other subquery thingies like that which seem to work fine.

Thanks for any help!

# ? Jul 22, 2010 10:01

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Doctor rear end in a top hat posted:

When you do an ORDER BY with a GROUP BY, the GROUP BY will keep the first record that the ORDER BY returns, plus calculate fields like counts. It should return the same thing as what you were getting, but with the newest dates. The whole query would look like this:
code:
SELECT MAX(count) as total, date_year, date_month, date_day
FROM (
    SELECT COUNT(DISTINCT user_id) AS count, date_year, date_month, date_day
    FROM logs
    WHERE action = "login" 
    AND date BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW()
    GROUP BY date_year, date_month, date_day
    ORDER BY date_year DESC, date_month DESC, date_day DESC
) AS countquery

I tried it, and it gave me the strange result of 2010, 7, 22, and ... 4? I'm running this at 9am, not 4am, so I don't know why it gave me 4... our peak hour certainly wasn't the middle of the night.

I think at this point I'll just dispense with the max and go with a simple order by. I'm still surprised there's no way to easily get the row from the max().

# ? Jul 22, 2010 15:27

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

Golbez posted:

I tried it, and it gave me the strange result of 2010, 7, 22, and ... 4? I'm running this at 9am, not 4am, so I don't know why it gave me 4... our peak hour certainly wasn't the middle of the night.

I think at this point I'll just dispense with the max and go with a simple order by. I'm still surprised there's no way to easily get the row from the max().

You're getting a count, a year, a month and a day. Where does the hour come in?

# ? Jul 22, 2010 15:30

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Doctor rear end in a top hat posted:

You're getting a count, a year, a month and a day. Where does the hour come in?

Er, sorry, I guess I omitted that earlier, we're running hour, day, month, etc. I guess the example I pasted was day, but the one I ran was hour. But that doesn't really solve the problem of the fact that the actual max wasn't today, and definitely not at 4am.

# ? Jul 22, 2010 15:39

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

What do the records look like when you just run the inner query?

# ? Jul 22, 2010 15:43

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Doctor rear end in a top hat posted:

What do the records look like when you just run the inner query?

A few examples of the day query:

code:

COUNT      date_year     date_month    date_day
-----------------------------------------------
198        2010          3             16
194        2010          3             8
183        2010          5             6

etc. So in the above set, I'd want the MAX() to just display 198, 2010, 3, 16. Right now I'm getting the same results by doing an ORDER BY COUNT, so the problem is technically solved, but I'm still curious if there's a way to get MySQL to display the row associated with MAX().

# ? Jul 22, 2010 15:56

Harry Krishna: Jul 2, 2004; I am not your body

I was wondering if there was any gui tool like sqlyog or the mysql one that issues a warning on update statements missing a where clause.

phpMyAdmin does this for delete statements, but I think updates are just as dangerous.

# ? Jul 22, 2010 19:18

baquerd: Jul 2, 2007; by FactsAreUseless

Harry Krishna posted:

I was wondering if there was any gui tool like sqlyog or the mysql one that issues a warning on update statements missing a where clause.

phpMyAdmin does this for delete statements, but I think updates are just as dangerous.

You could insert an update trigger that rejects them on a per-table basis. The logic for that would be a bit complex when you look at where clauses that could still include the entire data set, but you could get pretty close.

# ? Jul 23, 2010 06:29

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

Question Mark Mound posted:

As someone with very little experience with SQL, part of my job is to code SQL queries. My employers decided this was a good idea.

Anyway, when I run this MS SQL query I get "Incorrect syntax near the keyword 'GROUP'."
code:
WITH all_picks_refs 
     AS (SELECT DISTINCT LEFT(tkt_m_reference, 6) AS 'PickRef' 
         FROM   tkt_m_movements_tbl 
         WHERE  tkt_m_container = 'PICK') 
SELECT tkt_d_part_number, 
       COUNT(*) AS 'TotalPicks' 
FROM   (SELECT tkt_d_part_number, 
               pickref, 
               COUNT(*) AS 'Qty' 
        FROM   tkt_m_movements_tbl, 
               tkt_d_ticket_def_tbl, 
               all_picks_refs 
        WHERE  LEFT(tkt_m_reference, 6) = all_picks_refs.pickref 
               AND tkt_m_d_id = tkt_d_id 
               AND tkt_m_location = 'PICK' 
        GROUP  BY pickref, 
                  tkt_d_part_number) 
GROUP  BY tkt_d_part_number 
Basically, when I run the following query...
code:
WITH all_picks_refs 
     AS (SELECT DISTINCT LEFT(tkt_m_reference, 6) AS 'PickRef' 
         FROM   tkt_m_movements_tbl 
         WHERE  tkt_m_container = 'PICK') 
SELECT tkt_d_part_number, 
       pickref, 
       COUNT(*) AS 'Qty' 
FROM   tkt_m_movements_tbl, 
       tkt_d_ticket_def_tbl, 
       all_picks_refs 
WHERE  LEFT(tkt_m_reference, 6) = all_picks_refs.pickref 
       AND tkt_m_d_id = tkt_d_id 
       AND tkt_m_location = 'PICK' 
GROUP  BY pickref, 
          tkt_d_part_number 
I'm getting a list of of each part number in the warehouse, a pick reference (order number, essentially) of when someone bought one, and how many of that part were bought in that order.

What I need is to be able to count how many unique picks there are per part number. For example, if I get the results from the subquery:
PartNum___PickRef___Qty 12345_____ABCDEF____1 12345_____QWERTY____3 12345_____POIUYT____2 98765_____ABCDEF____1 98765_____LKJHGF____17
I want the final query to tell me that part 12345 has 3 unique picks for it, and 98765 has 2.

I'm not entirely sure why I can't do "SELECT whatever FROM (SELECT whatever....)" in this case, since I've got other subquery thingies like that which seem to work fine.

Thanks for any help!

T-SQL chokes if you don't give aliases to inline views.

code:

WITH all_picks_refs 
     AS (SELECT DISTINCT LEFT(tkt_m_reference, 6) AS 'PickRef' 
         FROM   tkt_m_movements_tbl 
         WHERE  tkt_m_container = 'PICK') 
SELECT tkt_d_part_number, 
       COUNT(*) AS 'TotalPicks' 
FROM   (SELECT tkt_d_part_number, 
               pickref, 
               COUNT(*) AS 'Qty' 
        FROM   tkt_m_movements_tbl, 
               tkt_d_ticket_def_tbl, 
               all_picks_refs 
        WHERE  LEFT(tkt_m_reference, 6) = all_picks_refs.pickref 
               AND tkt_m_d_id = tkt_d_id 
               AND tkt_m_location = 'PICK' 
        GROUP  BY pickref, 
                  tkt_d_part_number) tbl
GROUP  BY tkt_d_part_number

# ? Jul 23, 2010 18:52

Question Mark Mound: Jun 14, 2006; Tokyo Crystal Mew
Dancing Godzilla

Jethro posted:

T-SQL chokes if you don't give aliases to inline views.

code:

WITH all_picks_refs 
     AS (SELECT DISTINCT LEFT(tkt_m_reference, 6) AS 'PickRef' 
         FROM   tkt_m_movements_tbl 
         WHERE  tkt_m_container = 'PICK') 
SELECT tkt_d_part_number, 
       COUNT(*) AS 'TotalPicks' 
FROM   (SELECT tkt_d_part_number, 
               pickref, 
               COUNT(*) AS 'Qty' 
        FROM   tkt_m_movements_tbl, 
               tkt_d_ticket_def_tbl, 
               all_picks_refs 
        WHERE  LEFT(tkt_m_reference, 6) = all_picks_refs.pickref 
               AND tkt_m_d_id = tkt_d_id 
               AND tkt_m_location = 'PICK' 
        GROUP  BY pickref, 
                  tkt_d_part_number) tbl
GROUP  BY tkt_d_part_number

Wow, I expected it to be a much more severe problem than that. You're awesome, thanks!

# ? Jul 26, 2010 08:59

sonic bed head: Dec 18, 2003; this is naturual, baby!

I have a question about how mysqldump works. Does it freeze the database at the moment that it starts and dumps that frozen version or will it dump changes to tables as it gets to subsequent table names. I'm trying to find out how to take a snapshot of two different databases at the exact same instance to prevent inconsistencies inter and intra database. Can I just run mysqldump on both databases at the exact same time and expect the data to be consistent? Thanks for your help.

# ? Jul 28, 2010 15:38

No Safe Word: Feb 26, 2005

Looks like mysqldump doesn't lock anything itself:
http://dev.mysql.com/doc/refman/5.0/en/replication-howto-mysqldump.html

they suggest you run FLUSH TABLES WITH READ LOCK; prior to running mysqldump

# ? Jul 28, 2010 15:43

sonic bed head: Dec 18, 2003; this is naturual, baby!

No Safe Word posted:

Looks like mysqldump doesn't lock anything itself:
http://dev.mysql.com/doc/refman/5.0/en/replication-howto-mysqldump.html

they suggest you run FLUSH TABLES WITH READ LOCK; prior to running mysqldump

Oh I see. Then my question is what FLUSH TABLES WITH READ LOCK; does with insert or update statements. Does it queue them up to be executed after the lock is released? That would be my best case scenario, but I somehow doubt that it would be that easy.

# ? Jul 28, 2010 16:00

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

All queries ahead of it have to finish before it can lock. Make sure you don't have any big queries running when you do it.

edit: This link has more info.

butt dickus fucked around with this message at 16:18 on Jul 28, 2010

# ? Jul 28, 2010 16:13

sonic bed head: Dec 18, 2003; this is naturual, baby!

Doctor rear end in a top hat posted:

All queries ahead of it have to finish before it can lock. Make sure you don't have any big queries running when you do it.

edit: This link has more info.

That explains what happens before the lock, but what about what happens during the lock? I just added the read lock and then tried to insert and I got this error.

code:

Can't execute the query because you have a conflicting read lock

Is there a way where I wouldn't get this error but rather mysql would queue this new query and wait until the read lock was lifted to respond?

Basically I have a website that is constantly making updates and inserts and I can't bring down the whole site just to create a consistent backup from mysqldump.

# ? Jul 28, 2010 16:31

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

sonic bed head posted:

That explains what happens before the lock, but what about what happens during the lock? I just added the read lock and then tried to insert and I got this error.
code:
Can't execute the query because you have a conflicting read lock
Is there a way where I wouldn't get this error but rather mysql would queue this new query and wait until the read lock was lifted to respond?

Basically I have a website that is constantly making updates and inserts and I can't bring down the whole site just to create a consistent backup from mysqldump.

Well are the tables you are currently using INNODB?

Because there is an option to be able to get a consistent backup from innodb tables while they are online using a transaction that isn't possible with the default MyISAM type.

Have you ever used the MySQL Administrator tool?

# ? Jul 28, 2010 18:49

McGlockenshire: Dec 16, 2005; GOLLOCKS!

If you are running entirely InnoDB, you probably should look at Percona's "XtraBackup". It works without locking the entire database, and hey, it's OSS.

And if you aren't running InnoDB, fix that.

# ? Jul 28, 2010 21:18

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

I am using SQL Server 2005 and I was wondering what the best way to extract the latest auto increment.

So let me describe the situation, its very simple, I am adding rows to tables which contain an auto-increment, identity column, "nid".

What I want to do is extract the new "nid" value right after the insert. I know there are a few ways to do it.

code:

SELECT nid FROM table1 WHERE nid = @@identity

However, I read that this statement works across all scopes. Its just like saying

code:

SELECT MAX(nid) FROM table1

Then I read other things like SCOPE_IDENTITY, but to use it, you need to set up a "stored procedure" for each and every table that contains an auto-increment value. That seems way too excessive for such a simple task.

The other way, I think, is to set up a transaction before you insert, then as you end the transaction, extract out the new "nid" but that also seems like too many steps.

Anyone have any good ways of doing this. Unfortunately, I can't do a select statement to infer the "nid" based on other values in the table, there is repeated data in the table.

# ? Aug 2, 2010 22:31

Adbot: ADBOT LOVES YOU

# ? May 14, 2024 02:30

Bad Titty Puker: Nov 3, 2007; Soiled Meat

cannibustacap posted:

So let me describe the situation, its very simple, I am adding rows to tables which contain an auto-increment, identity column, "nid".

What I want to do is extract the new "nid" value right after the insert. I know there are a few ways to do it.

Then I read other things like SCOPE_IDENTITY, but to use it, you need to set up a "stored procedure" for each and every table that contains an auto-increment value. That seems way too excessive for such a simple task.

http://msdn.microsoft.com/en-us/library/ms190315.aspx

quote:

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Does that make sense? You don't have to set up a stored procedure, necessarily, just read SCOPE_IDENTITY in the same module where you INSERT into the table.

# ? Aug 3, 2010 04:31

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

«‹›303 »