How to order records correctly using DISTINCT

Asked

Viewed 406 times

0

In my table I have the column DESCRICAO and the DATA_DE_CADASTRO of the items. In my Android application I would like to display the last 10 records that were recorded.

There are a number of attributes that should be informed when saving a new item, so this listing serves for it, if you like, choose an item from which you can copy some attributes to the other item it is saving at the time since both items share the same information.

That way I would like to have the last 10 records distinctly because it doesn’t make sense, in this case, to present you a list with 10 repeated items.

I select by ordering it through the DTCAD column in descending mode.

SELECT DSBEM FROM TAB ORDER BY DTCAD DESC

And he brings me the following data:

inserir a descrição da imagem aqui

And this right. TELEFONE DIGITAL COM VISOR was the last item to be saved, but we have several repeated items, but if I select 

SELECT DISTINCT DSBEM FROM TAB ORDER BY DTCAD DESC

inserir a descrição da imagem aqui

Now it’s wrong. Where’s the digital phone?

I have already created the index for the description for dtcad, for the description and dtcad.

The command

SELECT DISTINCT DSBEM FROM ( 
SELECT DSBEM FROM TAB ORDER BY DTCAD desc ) LIMIT 10

also doesn’t work.

What could be done?

A possible solution would be this query.

SELECT DISTINCT DTCAD, DSBEM FROM TAB order by datetime(DTCAD) desc

but the return is this:

inserir a descrição da imagem aqui

sqlite

1 answer

0


Change to look like this:

SELECT DISTINCT DTCAD, DSBEM FROM TAB ORDER BY DTCAD DESC

What is happening is that when you put the distinct by denomination the phone that is repeated disappears because you do not also consider the registration date as a differentiating factor. This adjustment will consider as uniqueness factor denomination and date, making once the phone record appears again.

  • I had to change your query. Something like this: SELECT DISTINCT DTCAD, DSBEM FROM TAB ORDER BY DTCAD DESC That’s what you intended. It’s not?

  • But it still doesn’t work. In fact, I had already tried this. The query returns lines of the description repeated because there was a change in the registration date and thus passes through the DISTINCT sieve.

  • Strange too. Do the following, send the DER of this table of the bank. Put in the question the image,

  • I created a simple table with two columns: CREATE TABLE TAB (
 DSBEM VARCHAR(100) NOT NULL, 
 DTCAD DATETIME
); with a select in the main table and the same question occurs.

  • Test using: order by datetime(DTCAD) desc

  • The same thing happens. The description lines repeat because there was a change in the DTCAD column.

  • I posted the result of this query in the question.

  • There, do it now: select distinct DSBEM from (SELECT DISTINCT DTCAD, DSBEM FROM TAB order by datetime(DTCAD) desc).

  • Cool anyway: SELECT DISTINCT DSBEM FROM (SELECT DISTINCT DTCAD, DSBEM FROM TBL795 ORDER bY DTCAD DESC) LIMIT 10 worked.

Show 4 more comments

Browser other questions tagged sqlite

You are not signed in. Login or sign up in order to post.