c++qtsqlite

How to get data from sqlite after sorting?


I need your help once more.

I want to get the top 3 values from my sqlite database and store them in QStrings or int's.

My database has 1 table, and that table has 3 columns (name, wins, losses).

I want to do something like:

QString name1;
QSqlQuery qrry;
qrry.exec("SELECT name FROM users ORDER BY wins DESC WHERE rowid=1");

if (qrry.isActive())
{
    while(qrry.next())
    {
        name1=qrry.value(0).toString();
    }
}

If I remove ORDER BY wins DESC, I get the first name from db. But I want to sort it first and ORDER BY is not looking like the right way to do this.

Can you please help me?


Solution

  • As @hank said, your query should be:

    qrry.exec("SELECT name FROM users ORDER BY wins DESC LIMIT 3");
    

    LIMIT clause allows you to only get a limited number of results in your resultset. In this case, you will only get the name value of the 3 maximum values of wins column.

    The WHERE clause you wrote had no reason to be there.


    EDIT:

    how can I read the names like this: "name1=first name in db", "name2=2nd name", "name3=3rd name" using LIMIT ?

    If you want to get the 3 names in 3 different variables, you could write something like this:

    QString name1, name2, name3;
    
    if (qrry.isActive())
    {
        int i = 0;
        while(qrry.next())
        {
            switch(i)
            {
                case 0:
                    name1 = qrry.value(0).toString();
                    break;
                case 1:
                    name2 = qrry.value(0).toString();
                    break;
                case 2:
                    name3 = qrry.value(0).toString();
                    break;
            }
    
            i++;
        }
    }
    

    It is one way to do it but you could also put the names in an array or a vector. In your case, it doesn't really matter because you only want the 3 first names but if you wanted to keep more than 10 or 50 names, it would be easier to deal with arrays or vectors.

    LIMIT 3 clause ensures that you will have maximum 3 results in your resultset and that you will only loop 3 times in your while(qrry.next()) loop.