SQLTeam.com | Weblogs | Forums

Looking for automatic serial numbers 1,2,3 etc. in query

I have tables like this and I expect the result as shown below.

Untitled

I tried this query :slight_smile:

select row_number () over (order by level), table2.Level as Level, table2.Organization as Organization, max(nation) as nation from table2
left join table1 on table1.Level = table2.level and table1.organization = table2.organization
group by table2.level, table2.organization
order by nation, Level, Organization

But it doesn't produce the serial number in series.

How can you got 5 rows for level L3 in expected output?
you have onlly 4 rows for L3

SELECT ROW_NUMBER() OVER (ORDER BY [level]) As SrNo,
[level],
organisation FROM TESTT
WHERE [level] IN('L1','L3')

image

It is not working either.

Sorry, my sample data is not correct.

Actually, I have many columns and all other columns are from different table which are left joined. I will update the sample data.

I updated the sample data. Can you please check now?

Hi

I am working on your issue

Please post
-- Create Tables Script
-- Create Data Script

Not making any sense when I look at
the data

Thanks

Can you try this

select ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [Sl.No],
Table2.Level as Level,
Table2.Organization as Organization,
max(Table1.nation) as nation from Table2
left join Table1 on
Table1.Level = Table2.level
and Table1.organization = Table2.organization
group by Table2.level,
Table2.organization
having max(Table1.nation) is not null
order by Organization

Hi

Please find my solution below
Please let me know if it is correct

Query Solution

SELECT a.level,
a.organization,
Max(nation)
FROM table1 a
JOIN table2 b
ON a.level = b.level
AND a.organization = b.organization
GROUP BY a.level,
a.organization

Create Data Script

drop table table1
go

create table table1
(
Level varchar(10) null,
Organization varchar(10) null,
Nation int null
)

drop table table2
go

create table table2
(
Level varchar(10) null,
Organization varchar(10) null
)

insert into table1
values
('L1','PE',2),
('L1','PE',1),
('L2','PE',1),
('L2','QO',3),
('L2','PE',5),
('L3','PO',7),
('L3','PE',6),
('L3','PE',3)

insert into table2
values
('L1','PE'),
('L1','QO'),
('L1','PO'),
('L2','PE'),
('L2','QO'),
('L2','PO'),
('L3','PE'),
('L3','QO'),
('L3','PO')

He needs serial number as the first column in the output

Hi

Please find the corrections

Please let me know if this is OKAY

Corrected Script

SELECT row_number() over(order by a.level) as serialno,
a.level,
a.organization,
Max(nation) as Nation
FROM table1 a
JOIN table2 b
ON a.level = b.level
AND a.organization = b.organization
GROUP BY a.level,
a.organization

Thanks Ahmeds08,

Sorry, it didn't work either.

Hi Harish,

Thanks. This doesn't work because I have order by clause in my query. If I remove the order clause, it works fine but it sorts by first column.

order by nation, Level, Organization

Hi

Could you please post your query
so that I can look at it

with the order by clause that you
are talking about

I can try to play around with to find a solution

Thanks

Thanks, Harish.

This is my query

SELECT row_number() over(order by a.level) as serialno,
a.level,
a.organization,
Max(nation) as Nation
FROM table1 a
JOIN table2 b
ON a.level = b.level
AND a.organization = b.organization
GROUP BY a.level,
a.organization
orer by nation, level, organization

Hi

Does this help you ???

Script

SELECT
row_number()
over(order by a.level) as serialno,
a.level,
a.organization,
Max(nation) as Nation
FROM table1 a
JOIN table2 b
ON a.level = b.level
AND a.organization = b.organization
GROUP BY a.level,
a.organization
order by 4,3,2,1