What's new

SQL help required

Niggy G

HTAFC will rise again!!!
I’m designing a front end that will take a database of rom names (with a table for each system, n64, nes, etc) and populate a listbox.

This listbox has filters to filter JAP, EURO, USA, [!]. The filters are done using an SQL query.

I would like another filter that selects only unique games, e.g. the latest version of a game (Zelda OOT had 1.0, 1.1 etc). However I’m not sure how this could be done.

The only SQL query way I can think of is to compare the strings of games and then if the first so many characters are the same use the one with the higher index. (each record in the DB has an index). This won't work though because there a some games whose names are similar but they are different games (some sequels).

I wanted to use an SQL query as it would be quick, my only other thought would be to create a separate yes/no column in the database and check it if it is the unique version I want in the list. This would be very time consuming though especially as I want to do this for multiple systems.

I hope this wasn’t explained too poorly

Does anyone have any suggestions?
 

_Chrono_

aka Chrono Archangel
I beleive you can use something called "Distinct"

SELECT DISTINCT gameTitle
FROM TSNES;

you wont have the version though. cuz if you include the version then youll get the same result as a normal select without distinct
 
Last edited:
OP
Niggy G

Niggy G

HTAFC will rise again!!!
im using the Goodxxx naming convention but select distinct wouldn't work because if you look at the list below they are all different but i'd only want Legend of Zelda, The - Ocarina of Time (U) (V1.2) [!] from the list.

Legend of Zelda, The - Ocarina of Time (E) (GC Version) [!]
Legend of Zelda, The - Ocarina of Time (E) (GC Version) [f1]
Legend of Zelda, The - Ocarina of Time (E) (V1.0) [!]
Legend of Zelda, The - Ocarina of Time (E) (V1.0) [b1]
Legend of Zelda, The - Ocarina of Time (E) (V1.0) [f1](zpfc)
Legend of Zelda, The - Ocarina of Time (E) (V1.0) [f2](zpc1)
Legend of Zelda, The - Ocarina of Time (E) (V1.1) [!]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [!]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T+Dut]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T+Ita100]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T+Pol1.3]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T+Por1.5BetaFinal]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T+Rus101b2]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T+Spa01b_toruzz]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T+Spa097b2]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T+Spa1.0]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Pol1.2]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Por.09]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Por.14]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Por.22]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Por.26]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Por.28]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Por.30]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Por.33]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Por.35]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Por.37]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Por.42]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Rus.01]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Rus.06]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Rus.82]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Rus099bfix]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Rus099wip]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [b1]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [f1]
Legend of Zelda, The - Ocarina of Time (U) (V1.0) [f2]
Legend of Zelda, The - Ocarina of Time (U) (V1.1) [!]
Legend of Zelda, The - Ocarina of Time (U) (V1.1) [T+Ita100]
Legend of Zelda, The - Ocarina of Time (U) (V1.1) [T+Por100]
Legend of Zelda, The - Ocarina of Time (U) (V1.1) [T+Spa01b_toruzz]
Legend of Zelda, The - Ocarina of Time (U) (V1.1) [T+Spa1.0]
Legend of Zelda, The - Ocarina of Time (U) (V1.1) [b1]
Legend of Zelda, The - Ocarina of Time (U) (V1.1) [b2]
Legend of Zelda, The - Ocarina of Time (U) (V1.2) [!]
 

zAlbee

Keeper of The Iron Tail
Nice idea. It depends on the how consistent the naming convention is (I think it is pretty consistent, I've never kept more than one version of the same ROM though). You'll just have to look at all the strings for a pattern. For example, in Zelda OOT, you might want to parse the strings up until the first country code (U) or (E), etc. So if all ROM names follow the pattern of being equal before the country code, then you're all set.
 

Malcolm

Not a Moderator
I think it would be easier, and depending on the database faster, if you were to load the complete list then run the list through some regex statements. Using the SQL statement to get a unique name, specific country code and rom version from a single string seems a little more then just difficult.

If you want to do the selecting through the database anyways then you should format your input better; have rom-name, region-code, rom-version and rom-revision fields, for example, and sort your input through a regex statement to form your INPUT SQL statement.

:)
{EDIT}
RegEx code in action!

eg (php):
Code:
$input = "Legend of Zelda, The - Ocarina of Time (U) (V1.0) [T-Por.26]";
$replacement = preg_replace("#(.*) \((.*)\) \((.*)\) \[(.*)\]#", "INSERT INTO table (name, region, version, revision) values('\\1', '\\2', '\\3', '\\4')", $input);
The output from that would look like:
My Linux Shell said:
INSERT INTO table (name, region, version, revision) values('Legend of Zelda, The - Ocarina of Time', 'U', 'V1.0', 'T-Por.26')

Completely tested :p
 
Last edited:
OP
Niggy G

Niggy G

HTAFC will rise again!!!
Malcolm, thats a nice idea, but its over the top for what I want.

Speed wise, once the program has loaded all the list (on its inital start up) it runs any query very quickly. I think zAlbee's idea of coparing the string before the first '(' is the best way (if the naming convention is strict - i can't check at this computer) then if the string is the same use the one with the highest index. Thinking about that, is the one with the highest index always going to be the latest version? - I need the list in front of me to check.

Cheers for your input guys
 

Top