VGMdb
Go Back   VGMdb Forums > VGMdb Site Related > Questions and Comments
Register FAQ Calendar Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old Jul 11, 2012, 05:54 PM
Phonograph's Avatar
Phonograph Phonograph is offline
Senior Member
 
Join Date: Jun 2010
Posts: 4,329
Default about catalogue sorting

if you want catalogues to be sorted in natural order when you click the link to sort them
here's the sql (<catalog> if the keyword from the search)

SELECT catalog, SUBSTRING_INDEX(catalog, '-', -1) AS cn FROM `<database>`
WHERE catalog LIKE '<catalog>%' ORDER BY cn ASC

like that instead of having
KICA-1
KICA-1001
KICA-108

you will have
KICA-1
KICA-108
KICA-1001

Last edited by Phonograph; Jul 11, 2012 at 06:10 PM.
Reply With Quote
  #2  
Old Jul 11, 2012, 06:10 PM
Secret Squirrel's Avatar
Secret Squirrel Secret Squirrel is offline
VGMdb Administrator
 
Join Date: May 2007
Location: Cleveland, OH
Posts: 8,240
Default

That's a cool trick, though it would need to be more like this in the general case:
Quote:
SELECT *, SUBSTRING_INDEX(catalognum, '-', 1) AS pid1, SUBSTRING_INDEX(catalognum, '-', -1) AS pid2 FROM `vgmdb_albums` `t` WHERE `catalognum` LIKE '%ica%' ORDER BY pid1, pid2 ASC
The big question is whether it is worth making the change to all of the places we have a sort, or whether everyone is already accustomed to the standard ascii sort.
Reply With Quote
  #3  
Old Jul 11, 2012, 06:13 PM
Phonograph's Avatar
Phonograph Phonograph is offline
Senior Member
 
Join Date: Jun 2010
Posts: 4,329
Default

I don't get why you need two substring_indexes
because -1 means all numbers (theorically it should be numbers so that the numerical sorting works) after the - will be sorted naturally

you count before the - I guess?

Last edited by Phonograph; Jul 11, 2012 at 06:22 PM.
Reply With Quote
  #4  
Old Jul 11, 2012, 06:20 PM
Secret Squirrel's Avatar
Secret Squirrel Secret Squirrel is offline
VGMdb Administrator
 
Join Date: May 2007
Location: Cleveland, OH
Posts: 8,240
Default

KICA-1
PICA-2
KICA-108
MICA-500
KICA-1001

vs

KICA-1
KICA-108
KICA-1001
MICA-500
PICA-2
Reply With Quote
  #5  
Old Jul 11, 2012, 06:24 PM
Phonograph's Avatar
Phonograph Phonograph is offline
Senior Member
 
Join Date: Jun 2010
Posts: 4,329
Default

ah I see, it's because I never search for something like "ica" but by stem (KICA, VTCL, SQEX, etc.)
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Entertainment EXPO2002 Catalogue CD Secret Squirrel Album Discussions 3 Aug 24, 2011 03:08 PM
Sorting features for the wishlist LiquidAcid Questions and Comments 2 Sep 21, 2009 11:21 AM
Sorting marketplace albums by Added Date Kewing Questions and Comments 1 Sep 21, 2009 12:28 AM
Sorting out BlazBlue album differences Ramza Video Game Music Discussion 0 Jul 20, 2009 04:12 PM
How to find out catalogue number from JAN? Datschge Video Game Music Discussion 1 Jul 2, 2008 08:10 PM