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

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