How to retrieve data that is not in the same order as the query in SQL?

I am trying to retrieve a record from a table in SQL.

Here is what I want. For example:

I have a table name studentScore with two columns:

studentName -----  Scores
John Smith  ----- 75,83, 96

I want to do this: When I type the score in a search box, I want it to show me the name of the student. For example: I could type "83, 96, 75", (the scores can be in any order) and this should show me the student name "John Smith". But I'm wondering how we could specify 开发者_如何学Pythonin the WHERE clause so that it picks up the correct record, if what we type in the box is not in the same order as the original data in the column?

Your issue is that your data is not properly normalized. You are putting a 1 to n relationship into a single table. If you'd reorganize your tables like such:

Table Students

id  name
1   John Smith

Table Scores

studentId score
1         75
1         83
1         96

You could do a query like:

select st.name from Students st, Score sc where st.id = sc.studentId and sc.score in ("83", "75", "96")

This also helps if you want to do other queries, like find out which students have a score of at least X, which would be otherwise impossible with your existing table layout.

If you must stick with your existing layout, which I don't recommend, however you could split up the user input and then do a query like

select from studentScore where score like '%75%' or score like '%83%' or score like '%96%'

But i really would refrain from doing so.

I suppose it is solvable, but it would be simpler if the scores for each student were stored as separate rows, for example in a scores table. Otherwise, the code would have to permute the entry into every conceivable order. Or the scores entry would have to be in a standard order somehow.

If you do not want to create a new table for Scores, - e.g. with StudentId, Score columns -, you may sort the numbers before storing them.

This way, when someone types a query, you sort those numbers as well and just compare it to the stored strings.

If you need the original position of the scores, you can store those in a separate field.

Improve your database schema...this does not satisfy even the first normal form (http://en.wikipedia.org/wiki/Database_normalization#Normal_forms).

Improving the schema will save you plenty of headaches in the future (stemming from update anomalies).

No sql table should have multiple values for an attribute (in the same column). Are the scores stored as a string? If so, your query will be more complicated and you're wasting the point of the DB.

however, to your question:

SELECT col4, col3, col2 FROM students WHERE col1 = 57; 

this will return columns 4, 3, and 2 in that order (4,3,2) even if they are saved in the order 1, 2, 3, 4. SQL returns the things you ask for in the order you ask for them.

So yeah, I agree with everyone else that this design is crap. If you were to normalize this table properly, you would be able to very easily get the data you need.

However, this is how you could do it with the current structure. Split the user input into discrete scores. Then, pass each value into the procedure.

CREATE PROCEDURE FindStudentByScores
    (
     @score1 AS VARCHAR(3) = NULL
    ,@score2 AS VARCHAR(3) = NULL 
    ,@score3 AS VARCHAR(3) = NULL 
    )
AS 
    BEGIN
        SELECT  *
        FROM    [Students]
        WHERE   ( @score1 IS NULL
                  OR [Scores] LIKE '%' + @score1 + '%' )
                AND ( @score2 IS NULL
                      OR [Scores] LIKE '%' + @score2 + '%' )
                AND ( @score3 IS NULL
                      OR [Scores] LIKE '%' + @score3 + '%' )

    END 

You could use Regular expressions or the Like operator

A regexp solution could look like

SIMILAR TO '%(SCORE1|SCORE2|SCORE3)%' 

That's the easiest way to go but I recommend you changing your entire table structure as been mentioned now a couple of times, since you have no possibility to take advantage of an index or key which will exhaust the computer in matter of a couple tens of visitors

This is an example of where database normalization should help you a lot.

You could store your data like this (Edit: if you want to keep the order you can add an order column)

studentName        Scores    Order
John Smith         75        1
John Smith         83        2
John Smith         96        3
Foo bar            73        1
Foo bar            34        2
........

But if you are stuck with the current model your next best option is to have the Scores column sorted, then you just need to take the search string from the textbox, sort and format it correctly, then you can search.

Lastly if the scores is not sorted in the table you can create all possible combinations

75, 83, 96
75, 96, 83
83, 75, 96
83, 96, 75
96, 75, 83
96, 83, 75

and search for them all with OR.