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 »

Roundboy: Oct 21, 2008

Select user_id from join_table a
Left join user_table b on a.user_id = b.user_id
Where b.user_id is null

That is t the intent of what you want? All the IDs in join table that are not in the original table? Add a distinct if needed

# ? Apr 25, 2017 11:59

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 18:22

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

PhantomOfTheCopier posted:

LIKE is not a regular expression operator at all; it's a wildcard-based, pattern literal equivalence operator. It always matches the entire string, supports matching any single character in a given position, or a sequence of any characters, and otherwise matches every character individually and exactly.

In some sense it could be stated that LIKE supports the single character regexp atom, the dot atom(.), and the arbitrary quantified dot (.*) and nothing more.

Yeah LIKE to me was always "oh it's like DOS wildcards" and not "oh it's regex".

Did that ever improve for MSSQL? I remember hacking in regex-like functionality by converting data types to xml so many times.

# ? Apr 25, 2017 17:59

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Scaramouche posted:

Did that ever improve for MSSQL? I remember hacking in regex-like functionality by converting data types to xml so many times.

Not to my knowledge. :rant:

# ? Apr 25, 2017 19:25

ConanThe3rd: Mar 27, 2009

Roundboy posted:

Select user_id from join_table a
Left join user_table b on a.user_id = b.user_id
Where b.user_id is null

That is t the intent of what you want? All the IDs in join table that are not in the original table? Add a distinct if needed

Sorta did this but instead of using a join I basically used CodeIgniter to make variables out of the the SQL Searches needed.

It took far too long to figure out what was causing the PHP backend to spaz out (Turns out Codeignites uses mutidmential arrays as it's output which causes no end of fun)

:downsgun:

# ? Apr 25, 2017 20:42

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

Scaramouche posted:

Yeah LIKE to me was always "oh it's like DOS wildcards" and not "oh it's regex".

Did that ever improve for MSSQL? I remember hacking in regex-like functionality by converting data types to xml so many times.

T-SQL still doesn't have native regexes, but a less hacky option would be to use the CLR integration to do regexes or whatever you need in your query. I imagine it would be several orders of magnitude slower than LIKE, of course.

# ? Apr 25, 2017 22:21

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Student SQL project: Write a script that converts a regular expression into a collection of optimized SQL statements using Substr and Like. :downsrim:

PhantomOfTheCopier fucked around with this message at 00:03 on Apr 26, 2017

# ? Apr 26, 2017 00:00

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

NihilCredo posted:

T-SQL still doesn't have native regexes, but a less hacky option would be to use the CLR integration to do regexes or whatever you need in your query. I imagine it would be several orders of magnitude slower than LIKE, of course.

Yeah, been down that road. Unless it's improved the CLR mix-in was dog slow, though I haven't done it since MSSQL 2008R2.

# ? Apr 26, 2017 19:08

Rubellavator: Aug 16, 2007

Oracle has regex support that I never had issue with until the other day when I needed to write a regex with a [] expression that matched a ]. []abc], that's how you match a character that is a,b,c or a ]. The ] has to be the very first character. And if you need to match a hyphen it must be the first or last character. I've never encountered that convention anywhere else and really didn't understand why.

# ? Apr 26, 2017 20:06

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Sounds like POSIX regex, a kind of extension on the standard:
http://www.regular-expressions.info/posixbrackets.html

Pretty standard in most Unix-based implementations. You'll generally see non-POSIX in programming language regex implementations.

# ? Apr 26, 2017 20:38

Rubellavator: Aug 16, 2007

Ha, I'm a big dummy.

# ? Apr 26, 2017 20:58

Snak: Oct 10, 2005; I myself will carry you to the Gates of Valhalla...
You will ride eternal,
shiny and chrome.; Grimey Drawer

I'm trying to design a table that's a list words, grouped by whether they are synonyms.

So I have a field for the word itself, and a field for an integer that identifies the group. Words which are synonyms will have the same group number. The problem I'm having is that, when you need to add a word that isn't a synonym for any of the words already in the table, you need to get a new group number, and I don't know how to do this automatically.

Obviously, I could get all the unique values from the group number field and then look at the highest one and just add 1 to it. But that seems like a lot of work for something that is so simple conceptually.

Is there a much more efficient way to accomplish this?

# ? Apr 30, 2017 20:09

Kuule hain nussivan: Nov 27, 2008

Snak posted:

I'm trying to design a table that's a list words, grouped by whether they are synonyms.

So I have a field for the word itself, and a field for an integer that identifies the group. Words which are synonyms will have the same group number. The problem I'm having is that, when you need to add a word that isn't a synonym for any of the words already in the table, you need to get a new group number, and I don't know how to do this automatically.

Obviously, I could get all the unique values from the group number field and then look at the highest one and just add 1 to it. But that seems like a lot of work for something that is so simple conceptually.

Is there a much more efficient way to accomplish this?

You could have a helper table for the group-ids, but that honestly seems like an even bigger waste to me.

# ? Apr 30, 2017 20:23

Snak: Oct 10, 2005; I myself will carry you to the Gates of Valhalla...
You will ride eternal,
shiny and chrome.; Grimey Drawer

Kuule hain nussivan posted:

You could have a helper table for the group-ids, but that honestly seems like an even bigger waste to me.

Yeah, that's what I thought, too.

That might be the only good way to do it, though.

edit: I mean, I guess it doesn't to be an int or be human readable. I could just make it a varchar and make the group-id be the the first word added to it. I don't want to do that though, because there is a possible case where a word later gets removed after synonyms were added. Then, if the word was re-added, it would end up automatically going back into the group, regardless of whether it was intended to. That's... really sloppy.

edit2: It looks like I could just have an extra field... And autoincrementing primary key, the word field, and the group-id. When a word is entered into an existing word-group, set the group-id to the existing group-id, if it's a new word-group, set the group-id to the newly-formed auto-increment (I could do this in a second query in my PHP logic). Now, in InnoDB, there could still be a problem if a highnumber primary key gets deleted and a word with a lower ID somehow gets moved into that group. But that won't be an issue with MyISAM. And I can also just make it so that groups are never re-assigned and re-assignment is only ever done by removing the entry and then making a new one.

Snak fucked around with this message at 20:48 on Apr 30, 2017

# ? Apr 30, 2017 20:36

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Snak posted:

I'm trying to design a table that's a list words, grouped by whether they are synonyms.

So I have a field for the word itself, and a field for an integer that identifies the group. Words which are synonyms will have the same group number. The problem I'm having is that, when you need to add a word that isn't a synonym for any of the words already in the table, you need to get a new group number, and I don't know how to do this automatically.

Obviously, I could get all the unique values from the group number field and then look at the highest one and just add 1 to it. But that seems like a lot of work for something that is so simple conceptually.

Is there a much more efficient way to accomplish this?

If you don't already have a synonyms/groups/whatever table:

CREATE TABLE dbo.Synonyms
(
ID INT PRIMARY KEY IDENTITY(1,1)
, WordType nvarchar(50)
, AddedDttm datetime
);
GO

Then somewhere in your sproc:

IF NOT EXISTS
(SELECT 1 FROM dbo.Synonyms WHERE WordType = @wordType)
BEGIN
DECLARE @newIdentity int
INSERT INTO dbo.Synonyms (WordType, AddedDttm)
SELECT @wordType, current_timestamp
SET @newIdentity = SCOPE_IDENTITY
END

INSERT INTO dbo.Words (Word, synonymID, AddedDttm)
SELECT 'Word', @newIdentity, current_timestamp

Something like that is what I would do

# ? Apr 30, 2017 20:46

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

Snak posted:

edit: I mean, I guess it doesn't to be an int or be human readable.

If you want to be lazy you could make the synonym key a UUID and generate a new one when you add a new non-synonym.

NihilCredo fucked around with this message at 20:49 on Apr 30, 2017

# ? Apr 30, 2017 20:47

PierreTheMime: Dec 9, 2004; Hero of hormagaunts everywhere!; Buglord

Hey all, hopefully a simple question: What's the best method to return two values from the same column from the same table, the second row related by a key pulled from the first? I'm dealing with Oracle SQL more and more and I'm sure this is simple but I haven't done it yet.

Basically we have a table that has default object properties and then sub tables for object-specific info and we need to return the "name" of a user object related to another object "name" by the user who modified it.

# ? May 4, 2017 23:30

Tax Oddity: Apr 8, 2007; The love cannon has a very short range, about 2 feet, so you're inevitably out of range. I have to close the distance.

PierreTheMime posted:

Hey all, hopefully a simple question: What's the best method to return two values from the same column from the same table, the second row related by a key pulled from the first? I'm dealing with Oracle SQL more and more and I'm sure this is simple but I haven't done it yet.

Basically we have a table that has default object properties and then sub tables for object-specific info and we need to return the "name" of a user object related to another object "name" by the user who modified it.

I'm no Oracle expert, but if I were using SQL Server I'd probably use Lead or Lag. It looks like Oracle has something similar. https://oracle-base.com/articles/misc/lag-lead-analytic-functions.

# ? May 4, 2017 23:41

Gatac: Apr 22, 2008; Fifty Cent's next biopic.

PierreTheMime posted:

Hey all, hopefully a simple question: What's the best method to return two values from the same column from the same table, the second row related by a key pulled from the first? I'm dealing with Oracle SQL more and more and I'm sure this is simple but I haven't done it yet.

Basically we have a table that has default object properties and then sub tables for object-specific info and we need to return the "name" of a user object related to another object "name" by the user who modified it.

I don't know precisely what you're looking to do here. If the second row is offset from the first by some fixed amount as can be worked out in the window of an analytic funtion's window, then yeah, LEAD or LAG could do the trick. If there's a column in the first row that references a second row, you're probably looking for hierarchical queries.

# ? May 5, 2017 07:43

PierreTheMime: Dec 9, 2004; Hero of hormagaunts everywhere!; Buglord

Gatac posted:

I don't know precisely what you're looking to do here. If the second row is offset from the first by some fixed amount as can be worked out in the window of an analytic funtion's window, then yeah, LEAD or LAG could do the trick. If there's a column in the first row that references a second row, you're probably looking for hierarchical queries.

It's definitely not a specific offset, unfortunately.

This is for a system where all possible functions are objects. A script to execute is an object, a user is an object, a calendar is an object, etc. The main table contains all default object properties such as "object name" and an identifying number to use to cross reference with other tables for more specific information such as "user first name" which is stored in a separate user table. What I'm trying to do is search for an object type and find what user modified it last. The problem being that the object type and the user both need to get the name from the same table from the same column, just a different row based on the "moduserid" which is a value on the row of the object type. I don't know how to select the same column from the same table twice and get different results based on a value on a secondary column from the first object.

I'll edit in an example query and the result when I get to my desk.

# ? May 5, 2017 13:13

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

PierreTheMime posted:

It's definitely not a specific offset, unfortunately.

This is for a system where all possible functions are objects. A script to execute is an object, a user is an object, a calendar is an object, etc. The main table contains all default object properties such as "object name" and an identifying number to use to cross reference with other tables for more specific information such as "user first name" which is stored in a separate user table. What I'm trying to do is search for an object type and find what user modified it last. The problem being that the object type and the user both need to get the name from the same table from the same column, just a different row based on the "moduserid" which is a value on the row of the object type. I don't know how to select the same column from the same table twice and get different results based on a value on a secondary column from the first object.

I'll edit in an example query and the result when I get to my desk.

If I'm understanding correctly it sounds like you need something like (assuming there is some sort of modifiedtime field in your table)

code:

SELECT x.objecttype, 
       u.USER 
FROM   dbo.users u 
       JOIN (SELECT TOP 1 objecttype, 
                          moduserid 
             FROM   dbo.mytable 
             WHERE  objecttype = 'Whatever' 
             ORDER  BY modifiedtime DESC) x 
         ON u.userid = x.moduserid

I'm not an Oracle guy so hopefully there's a way to do this?

# ? May 5, 2017 13:49

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

PierreTheMime posted:

It's definitely not a specific offset, unfortunately.

This is for a system where all possible functions are objects. A script to execute is an object, a user is an object, a calendar is an object, etc. The main table contains all default object properties such as "object name" and an identifying number to use to cross reference with other tables for more specific information such as "user first name" which is stored in a separate user table. What I'm trying to do is search for an object type and find what user modified it last. The problem being that the object type and the user both need to get the name from the same table from the same column, just a different row based on the "moduserid" which is a value on the row of the object type. I don't know how to select the same column from the same table twice and get different results based on a value on a secondary column from the first object.

I'll edit in an example query and the result when I get to my desk.

Self join?

code:

select objtypes.objectname object_type_name,
             users.objectname last_modified_by
  from objects objtypes
       inner join objects users on objtypes.moduserid = users.objectid
 where objtype.type= 'OBJECT_TYPE'

You're using the same table twice for two different things, so you use the table twice as if they were two different tables.

# ? May 5, 2017 15:11

PierreTheMime: Dec 9, 2004; Hero of hormagaunts everywhere!; Buglord

Here's an example of what I have and what else I'm trying to retrieve:

code:

select OH_Name as ObjectName, OH_ModDate as ModifiedDate, OH_ModUserIdnr as ModUserId from OH O, USR U
where O.OH_OType = 'JSCH' 
and O.OH_Client = &$CLIENT# 
and O.OH_ModDate >= SYSDATE-7 
and U.USR_OH_Idnr = O.OH_ModUserIdnr 
and U.USR_FirstName not like 'One'

Example result:

code:

OBJECTNAME				MODIFIEDDATE			MODUSERID
MAT.SCH.DAILY_EST.OLD.20170502.195334	2017-05-02 19:45:26.000		1231007
MAT.SCH.DAILY_EST			2017-05-02 19:53:34.000		1231007
MAT.SCH.SLEEP_05			2017-05-04 14:58:05.000		1231019
MAT.SCH.SLEEP_05.OLD.20170504.144048	2017-05-04 14:30:47.000		1231019
R33.SCH.YIEOR.OLD.20170502.190701	2017-05-02 19:07:00.000		1231012

Here's a secondary example of what other OH_Name value I'm hunting for, based off the OH_ModUserIdnr:

code:

select OH_Name from OH O
where O.OH_IDNR = 1231007

Result:

code:

999999/CORP

The problem with this is that I'm not sure how to have it search for a result based on a value from the first returned row. If it was a static value I could set a variable to reference, but I'm not experienced enough to do it with multiple values. The ultimate goal would be to have the value of the user's object name returned in a separate column, something like this:

code:

OBJECTNAME				MODIFIEDDATE			USERID
MAT.SCH.DAILY_EST.OLD.20170502.195334	2017-05-02 19:45:26.000		999999/CORP

I'm quite new to SQL and I apologize if these posts come off as dumb. :/

# ? May 5, 2017 15:49

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Does Oracle SQL have CTEs? Sounds like you need to assign a rank to each object based on modifieddate and select the top 1 from each and join it to your user table

Something like

code:

;WITH ranking (objectname, modifieddate, moduserid, rank) 
     AS (SELECT oh_name, 
                oh_moddate, 
                oh_moduseridnr, 
                Row_number() 
                  OVER ( 
                    partition BY oh_name 
                    ORDER BY oh_moddate DESC) 
         FROM   oh 
         WHERE  o.oh_client = &$client# 
                AND o.oh_moddate >= sysdate-7) 
SELECT r.objectname, 
       o.modifieddate, 
       o.moduserid 
FROM   ranking r 
       JOIN usr u 
         ON r.moduserid = u.usr_oh_idnr 
WHERE  r.rank = 1 
       AND U.usr_firstname NOT LIKE 'One'

If you can't use a cte then a temp table should suffice instead I would think

e: missed a from clause, oops

kumba fucked around with this message at 22:08 on May 5, 2017

# ? May 5, 2017 16:22

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

PierreTheMime posted:

[snip]

It's not 100% clear what output you're expecting. Based on your question, there are two options.

code:

OBJECTNAME				MODIFIEDDATE			USERID
MAT.SCH.DAILY_EST.OLD.20170502.195334	2017-05-02 19:45:26.000		999999/CORP
MAT.SCH.DAILY_EST			2017-05-02 19:53:34.000		999999/CORP
MAT.SCH.SLEEP_05			2017-05-04 14:58:05.000		111111/Blah
MAT.SCH.SLEEP_05.OLD.20170504.144048	2017-05-04 14:30:47.000		111111/Blah
R33.SCH.YIEOR.OLD.20170502.190701	2017-05-02 19:07:00.000		222222/Butts

code:

OBJECTNAME				MODIFIEDDATE			USERID
MAT.SCH.DAILY_EST.OLD.20170502.195334	2017-05-02 19:45:26.000		999999/CORP
MAT.SCH.DAILY_EST			2017-05-02 19:53:34.000		999999/CORP
MAT.SCH.SLEEP_05			2017-05-04 14:58:05.000		999999/CORP
MAT.SCH.SLEEP_05.OLD.20170504.144048	2017-05-04 14:30:47.000		999999/CORP
R33.SCH.YIEOR.OLD.20170502.190701	2017-05-02 19:07:00.000		999999/CORP

If it's the first, that's a self-join.

code:

select O.OH_Name as ObjectName, O.OH_ModDate as ModifiedDate, OU.OH_Name as UserId from OH O, USR U,
OH OU
where O.OH_OType = 'JSCH' 
and O.OH_Client = &$CLIENT# 
and O.OH_ModDate >= SYSDATE-7 
and U.USR_OH_Idnr = O.OH_ModUserIdnr 
and U.USR_FirstName not like 'One'
and OU.OH_IDNR = O.OH_ModUserIdnr

If it's the second, you need the self-join plus some way to determine what the "first" row is, using ranking functions and a CTE or subquery (like kumba suggested).

Jethro fucked around with this message at 22:01 on May 5, 2017

# ? May 5, 2017 21:57

Mush Man: Jun 25, 2010; Nintendo announces Frolf means Frog Golf.; Oven Wrangler

I'm trying to set up a one-way one-to-one database replication process, but I don't really know what I'm doing and our preferred layout isn't totally standard. We have a live database on our production server, and we need to sync data from a remote server to a new database that'll be on the production server. Both the fresh remote and local syncing databases have no tables or data yet.

When I save the configuration file on the production server with the intention for it to only affect the slave database, the live one appears like it's trying to connect to the remote database. A quick rollback undoes this, but of course there's no replication. I've followed guides like these and tried to search my issue but had no luck.

The documentation mentions ignore filters, however it's vague. Which server's config should which option be in? In the context of each option, am I supposed to specify a database from the local or remote server? Each attempt I've tried has failed.

To summarise:

SQL code:

PRODUCTION SERVER
production_live   --Application data. Replication should not affect this.
production_sync   --Slave database to replicate to.

REMOTE SERVER
remote_sync       --Master database where new data comes in to replicate from.

Is what I'm trying to do possible? Should I be trying to set this up some other way entirely?

Edit: Decided to abandon this approach and try another method. All good now.

Mush Man fucked around with this message at 05:41 on May 12, 2017

# ? May 11, 2017 12:06

xenilk: Apr 17, 2004; ERRYDAY I BE SPLIT-TONING! Honestly, its the only skill I got other than shooting the back of women and calling it "Editorial".

Hey guys, i have a bit of a abstract question which im hoping you can help.

At my new job the DBA guy is saying that running Analyze/Optimize on all tablea is impossible because it takes almost two full days. From what I can tell they have a relativly small architecture (12 schemas with less than 150 tables total). They however have a few tables with billions of rows.

Even with that said it strikes me as odd especially knowing that the master server has 1tb of ram and lots of cpu.

Im thinking that they might be overdoing indexes, but other than that is there anything else I should explore?

# ? May 11, 2017 21:45

spiritual bypass: Feb 19, 2008; Grimey Drawer

How do you know the indexes are overloaded? Are there a bunch that don't get hit often in queries?

# ? May 11, 2017 22:06

xenilk: Apr 17, 2004; ERRYDAY I BE SPLIT-TONING! Honestly, its the only skill I got other than shooting the back of women and calling it "Editorial".

rt4 posted:

How do you know the indexes are overloaded? Are there a bunch that don't get hit often in queries?

I don't know yet but it s what came to mind when he told me they never do optimize the tables nor update the tables statistics because it takes too long. Im wondering if there are other things i should potentially investigate since ill most likely be tasked with it.

# ? May 11, 2017 22:10

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Tell them to move to a real database where this isn't a problem?

If you've no option to update statistics, you'll have to check the performance tables, index reads, hits, cache reads, and so forth. If you can't find a cause, and maybe even if you can, you'll have to redesign some stuff.

But yeah moving off grep is the first step. :buddy:

# ? May 11, 2017 23:31

Condimentalist: Jun 13, 2007

Are there any good free entity relationship diagramming programs out there?

Specifically trying to improve / completely replace an existing schema that an ex-employee created. It is terrible.

# ? May 12, 2017 13:01

huhu: Feb 24, 2006

Does anyone have a suggestion for decent MySQL tutorials that don't have an insufferable sounding teacher?

# ? May 12, 2017 18:05

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Condimentalist posted:

Are there any good free entity relationship diagramming programs out there?

Specifically trying to improve / completely replace an existing schema that an ex-employee created. It is terrible.

http://www.codediesel.com/data/5-tools-to-visualize-database-schemas/

Out of all these I've only used MySQL Workbench, which is p cool, but obviously limited to MySQL.

# ? May 12, 2017 18:07

A MIRACLE: Sep 17, 2007; All right. It's Saturday night; I have no date, a two-liter bottle of Shasta and my all-Rush mix-tape... Let's rock.

Huge noob Postgres question inbound:

SQL code:

myapp::DATABASE=> refresh materialized view X;
ERROR:  could not write block 386 of temporary file: No space left on device

can someone help me I've never seen this before. It's a PG database via Heroku if that makes any difference. Obviously I'm out of space but I have no idea where to clean it up. Running VACUUM didn't help.

# ? May 14, 2017 01:52

spiritual bypass: Feb 19, 2008; Grimey Drawer

I don't know the technical details of VACUUM, but if it needs to write any bytes before deleting, it's gonna fail until you can free up some space. Does Heroku let you add space incrementally?

# ? May 14, 2017 22:46

Roundboy: Oct 21, 2008

I believe the full vacuuming needs to make a copy of the database to clean unused bits. So if you got zero space that will be a problem

# ? May 15, 2017 01:09

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

That's a question for Heroku. Contact them to request more storage for your database so you can continue cleanup. You're unlikely to recover space with VACUUM because auto-vacuum is the default. It's possible space is being taken by extensive logging, but that's a property of the configuration, not the data.

Surely you got an alarm indicating you were running out of space.

vvv Still on it? Well you have to get access to the hosting server. Otherwise the only safe way to reclaim space will be to drop indexes and such, but I can't guarantee that will flush or that your app won't fill it all back up in ten seconds.

Last time I had to deal with this (four weeks ago) I built a larger server as a replica and migrated, but yeah... cleanup is ongoing.

PhantomOfTheCopier fucked around with this message at 23:32 on May 15, 2017

# ? May 15, 2017 07:18

A MIRACLE: Sep 17, 2007; All right. It's Saturday night; I have no date, a two-liter bottle of Shasta and my all-Rush mix-tape... Let's rock.

I opened a ticket. The startup I'm with now is really poor so we can't afford to pay for premium support lol

# ? May 15, 2017 07:55

huhu: Feb 24, 2006

I've got a table with historical data where each time someone edits a row in the Flask app, a new row is added to the table instead of editing it in place. Here is what the entire table looks like:

code:

idx	Foo	Value	OtherCol	OtherCol2	OtherCol3	...and so on
0	A	0
1	C	1															
2	B	0															
3	A	0
4	A	2															
5	B	1

How could I do a query so that I only get only one Value per Foo and that Value is based on the most recent idx? Desired result:

code:

idx	Foo	Value	OtherCol	OtherCol2	OtherCol3	...and so on
1	C	1																										
4	A	2															
5	B	1

I currently have an ugly way to do this with two SQL queries and Python but was curious if there is a single query to do it.

# ? May 23, 2017 18:43

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

code:

SELECT
    tableOuter.idx, tableOuter.Foo, tableOuter.Value, [other columns]
FROM
    (SELECT Foo, MAX(idx) AS maxIdx FROM theTable GROUP BY Foo) AS subq
    JOIN theTable AS tableOuter ON subq.maxIdx = tableOuter.idx

# ? May 23, 2017 19:16

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 18:22

huhu: Feb 24, 2006

Hammerite posted:

code:

SELECT
    tableOuter.idx, tableOuter.Foo, tableOuter.Value, [other columns]
FROM
    (SELECT Foo, MAX(idx) AS maxIdx FROM theTable GROUP BY Foo) AS subq
    JOIN theTable AS tableOuter ON subq.maxIdx = tableOuter.idx

Impressive, thanks!

# ? May 23, 2017 22:14

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

«‹›303 »