SQL query, not show report if previous value is same

Transact-SQL
1

Need help,

Here is returned data:

Screenshot_5

Please see picture.
I want see 'blank' row, if those tree row:s are same. I mean, all row:s still here but double row is without any value (blank).

Is this possible?
Can't use Lag() because solid server.

Thanks

2

need more info than this. DDL, sample data and expected output would help as well. Don't know what you mean by this:

I want see 'blank' row, if those tree row:s are same. I mean, all row:s still here but double row is without any value (blank).

1 Like
3

Screenshot_5
Screenshot_5589×647 124 KB

Here is what i mean.

SQL Query:

SELECT DISTINCT TYA_TYONRO, TYA_ALKUKLO, TYA_LOPPUKLO, MTR_RIVINRO, TYA_TEKIJA, MTR_TYOKOODI, MTR_TEHTAVA, MTR_ARVIOAIKA, IFNULL(TIMESTAMPDIFF(1,TYA_ALKUKLO,TYA_LOPPUKLO),0)/3600 ID9
FROM TYOAIKA 
INNER JOIN MAARTYO ON MTR_NRO = TYA_TYONRO
GROUP BY TYA_TYONRO, TYA_ALKUKLO, TYA_LOPPUKLO, MTR_RIVINRO, TYA_TEKIJA, MTR_TYOKOODI, MTR_TEHTAVA, MTR_ARVIOAIKA
ORDER BY TYA_ALKUKLO ASC
4

What you are asking for is normally done in the reporting/application layer - not in the data layer or database.

Since you haven't provide any useful sample data - all I can do is generalize an approach. One approach would be to add a row_number for each 'group', then check the row number value and 'blank' any columns where that row number is not equal to 1.

It is quite useless to use both DISTINCT and GROUP BY in the same query. Either one or the other and since you are not aggregating any data - no reason to use a GROUP BY. Further - using DISTINCT shouldn't be necessary if your join criteria and filters are correctly set.

Finally - since this site is for Microsoft SQL Server and you are definitely not using that product you may not get a solution that will work on your flavor of SQL.

2 Likes
5

so what @mike01 and @jeffw8713 are asking you can be provided as follows. Since we do not have access to your server/database, unless you provide sample data it would very hard to help you

so provide your data not as an image but as follows

declare @zimzam table(MTR_NRO int,  TYA_ALKUKLO string,  --etc

insert into @zimzam
values(49186, '1.6.2022 7:29:00', --etc

making sure to provide all of the data. That way we have your data and we can use it in our sql server and help us help you

1 Like
6

Thanks for messages.

Sorry i don't know how i can provide sample data here? (copy txt like .csv here?).

7

here it is repeated again.

declare @sampledata table(MTR_NRO int,  TYA_ALKUKLO string,  --etc

insert into @sampledata
values(49186, '1.6.2022 7:29:00', --etc

like the following post as an example

8

Your code doesn't look like it's for SQL Server.

Which dbms (MySQL, Postgre, etc.) are you using?

9

hi
hope this helps

i know this post is from 7 months ago
lets say time pass , SQL enthusiast ( what ever )

used my own sample data .. but to illustrate

Jeff above suggested this approach also

create my own sample data script

create table #SampleData (id int ,id2 int ,id3 int ,name varchar(10),rn int )

insert into #SampleData select 1,200,45,'Pam',1
insert into #SampleData select 1,200,45,'Pam',2

insert into #SampleData select 2,300,55,'Pam',2
insert into #SampleData select 2,300,55,'Pam',3
insert into #SampleData select 2,300,55,'Pam',5
insert into #SampleData select 2,300,55,'Pam',8

insert into #SampleData select 3,600,67,'Pam',1
insert into #SampleData select 3,600,67,'Pam',2

 ; with cte as 
     (select ROW_NUMBER() over(partition by id,id2,id3,name order by rn) as rn1,* from #SampleData ) 
select 
     id
   , case when rn1 =1 then cast(id2 as varchar)  else '' end 
   , case when rn1 =1 then cast(id3 as varchar)  else '' end 
   , case when rn1 =1 then name                  else '' end 
   , rn   
from cte

image