CODEWITHSUNDEEP
c++qtsqlite
Alex
Alex

Reputation: 107

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?

Upvotes: 1

Views: 279

Answers (1)

Aleph0
Aleph0

Reputation: 470

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.

Upvotes: 5

Related Questions