SQLTeam.com | Weblogs | Forums

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


#1

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.


#2

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


#3

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.


#4

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


#5

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


#7

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


#8

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')


#9

He needs serial number as the first column in the output


#10

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


#11

Thanks Ahmeds08,

Sorry, it didn't work either.


#12

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


#13

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


#14

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


#15

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