Need SQL to Sort table based on another list of array

Question

I need to get list of item from my table but in sorted order and list must be sorted as below:

Assume I have fields named as "productid" and "productname" and table as below records

1. milk
2. pizza
3. boll

And I have another list in array such as

boll
pizza
milk

Now I want to sort table list based on array value and get below list"

3. boll
2. pizza
1. mike

Is there any SQL which can do what I need? Or I need to do it manually via coding.

chamakits · Accepted Answer · 2013-06-15 16:43:46Z

5

You can do this if with the following bit of pseudo code, from your application code

sqlexecuter.run(
"CREATE TEMPORARY TABLE ORDERED_ENTRIES(
       ORDER_ENTRY NOT NULL AUTO_INCREMENT PRIMARY KEY,
       PRODUCT_NAME varchar(255) --Put the same amount size as your other table
)")

Then in your code insert them into that table, in a similar way to this

for(i:lenght(myProductArray))
    sqlexecuter.run
    ("
        INSERT INTO ORDERED_ENTRIES
        VALUES("+myProductArray[i]+")"
    )

And then select like this

sqlexecuter.select(
"
SELECT OE.ORDER_ENTRY, PT.PRODUCT_NAME
FROM PRODUCT_TABLE AS PT, ORDERED_ENTRIES AS OE
WHERE
    OE.PRODUCT_NAME = PT.PRODUCT_NAME
ORDER BY OE.ORDER_ENTRY
");

answered Jun 15, 2013 at 16:43

chamakits

1,9351 gold badge17 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chamakits Over a year ago

As an extra tip, you should do a batch insert if you can, to insert everything in one go. Similar to this one: stackoverflow.com/questions/10429348/batch-insert-sql-statement

Collectives™ on Stack Overflow

Need SQL to Sort table based on another list of array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related