Avatar billede fredand Forsker
26. august 2019 - 12:59 Der er 8 kommentarer

How to select distinct rows group by

Hello Guys
I got a table like
id, date, message
With a lot of messages per day

I need to select distinct message per day, not per date.

Do you guys see a nice SQL for that?
Best regards
Fredrik
Avatar billede arne_v Ekspert
26. august 2019 - 14:22 #1
SELECT DISTINCT date,message FROM table

or

SELECT DISTINCT DATE(date),message FROM table

(I am assuming the date field is not really called date)
Avatar billede fredand Forsker
26. august 2019 - 15:22 #2
Hello Arne,
Thanks a lot, but I did not gave you the whole picture, as usually.Sorry for that.
It may look like this
1, x, 2019-08-26 12:44
2, x, 2019-08-26 12:45
3, x, 2019-08-26 12:46
4, y, 2019-08-26 12:47
5, z, 2019-08-26 12:48

I need to select to get a result like this:
x, 2019-08-26
y, 2019-08-26
z, 2019-08-26

Best regards
Fredrik
Avatar billede arne_v Ekspert
26. august 2019 - 15:33 #3
And:

SELECT DISTINCT DATE(date),message FROM table

doesn't do that?
Avatar billede fredand Forsker
27. august 2019 - 12:53 #4
Hello Arne,
That question gave me a list like:

2019-08-26 16:00    x
2019-08-26 16:01    x
2019-08-26 16:03    x
2019-08-26 16:06    x
2019-08-26 15:55    y
2019-08-26 15:54    y
2019-08-26 15:58    y
2019-08-26 15:58    y

Could this be since there probably is some millisec that makes each row unique?
Avatar billede fredand Forsker
27. august 2019 - 12:56 #5
Aha, wait minute I think I missed DATE(date), back in a minute
Avatar billede fredand Forsker
27. august 2019 - 13:16 #6
Hmm... my Oracle seems not to like the DATE()-call.

So would you say that this is wrong (it gives my duplicate rows like above):

SELECT DISTINCT(tidpunkt), meddelande FROM my_table;
WHERE
    tidpunkt between sysdate-10 AND sysdate
AND
      meddelande like '%MyMeddelande%'
ORDER BY meddelande;

Btw tidpunkt is a Date,
Avatar billede fredand Forsker
27. august 2019 - 13:33 #7
Sorry but missed to remove a semicolon.Sorry for spamming!
So would you say that this is wrong (it gives my duplicate rows like above):
SELECT DISTINCT(tidpunkt), meddelande FROM my_table
WHERE
    tidpunkt between sysdate-10 AND sysdate
AND
      meddelande like '%MyMeddelande%'
ORDER BY tidpunkt;

Btw I do not I think it is possible to just get it per day if there are several rows spread among a day at different times.
Avatar billede arne_v Ekspert
27. august 2019 - 16:06 #8
Nyt forsoeg:

SELECT DISTINCT TO_CHAR( tidpunkt, 'YYYY-MM-DD'), meddelande FROM my_table
WHERE
    tidpunkt between sysdate-10 AND sysdate
AND
      meddelande like '%MyMeddelande%'
ORDER BY TO_CHAR( tidpunkt, 'YYYY-MM-DD');
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester